I have one spreadsheet in Google Sheets with data like: In sheet 1 in column pricelist

$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. Not able to understand how to make the change.

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

The formula used to convert text to number:

  • 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, privacy policy and cookie policy

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