I have a spreadsheet in Google Sheets with data like this:
In sheet 2, in column pricelist, the below data is there. I want the summary in sheet 1 to have the same. I have used index and match but it is not working.

$100 in text
102 in Number
$103 in text
104 in number
105 in number

When I do the vlookup and try to multiply with 1 it is showing an error. I don't understand how to make the change.

I have used this formula to convert text to numbers, but if the cell has a number format then it is showing an error.

The formula used to convert text to numbers:


1 Answer 1

  • to convert text to numbers use this:

 VALUE(SUBSTITUTE(REGEXEXTRACT(TO_TEXT(A1:A), "\d+,\d+|\d+.\d+|\d+"), ",", ".")), ))


  • for non-US syntax try:

 VALUE(SUBSTITUTE(REGEXEXTRACT(TO_TEXT(A1:A); "\d+,\d+|\d+.\d+|\d+"); "."; ",")); ))


  • if you are not sure which syntax to use, take this:

 VALUE(SUBSTITUTE(REGEXEXTRACT(TO_TEXT(A1:A); "\d+,\d+|\d+.\d+|\d+"); ","; "."));
 VALUE(SUBSTITUTE(REGEXEXTRACT(TO_TEXT(A1:A); "\d+,\d+|\d+.\d+|\d+"); "."; ","))); ))


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.