Google-sheets – Convert Number and Text to Number

formulasgoogle sheetsgoogle-sheets-arrayformularegexregexextract

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:

=roundup(REGEXREPLACE(index(Sheet1!A:A,match(E3,Sheet1!A:A,0),""),"[$,*]",),)

Best Answer

  • to convert text to numbers use this:

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

0

  • for non-US syntax try:

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

5

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

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

Related Topic