VALUE parameter cannot be parsed to number (Google SpreadSheet Error)

Question

I`m trying to extract from this url: https://www.instagram.com/fcbarcelona/ the value of posts amount, this one: http://prntscr.com/p29wqn using this formula in google spreadsheet:

=VALUE(REGEXEXTRACT(IMPORTXML(A25,"//meta[@name='description']/@content"),"(?:Following, )(.{1,})(?: Posts)"))

but i get this massage:

Error VALUE parameter '12.1k' cannot be parsed to number.

Tell please how could I fix the issue?

Thanks

Answer 1

This is happening as you are passing the string that does have a non-numeric value to the VALUE method, which expects only numeric string.

If you really want to convert 12.1k to number then you can do something like below (consider that the value 12.1k is stored in B20 cell)

=VALUE(IF(MID(B20,LEN(B20),1)="k",MID(B20,1,LEN(B20)-1)*1000,B20))

Screenshot: