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 that you have read and understand our privacy policy and code of conduct.

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