0

I need to transform text in a cell to numeric value. The text is typed so I need to ingrore capitals and spaces. Ideally to use if cell contains

  • text car = 350
  • text Mobile data = 120
  • text car, mobile data = 350 + 120
  • text mobile data,car = 120 + 350

can I do that by one formula? Not by google script.

UPDATE

the final working solutin is

=350 * regexmatch(lower(A2), "car") + 120 * regexmatch(lower(A2), "mobile data")

so the replaced numbers can be summed

1

1 Answer 1

1

If your expected result is a text string like 120 + 350, try this:

=regexreplace( regexreplace( regexreplace( regexreplace( lower(trim(A2)), "car", "350" ), "mobile data", "120" ), "\s*,\s*", " + " ), "[^+\d ]", "" )

If that is not what you wanted, please follow Rubén's advice, show your expected results, and also consider The correct way to publicly share a Google test sheet.

9
  • It does exactly what I wanted. Just ... it does not work in my original sheet. It gives me formula error. I am not able to reproduce it so I can share it. i.imgur.com/QGsVO8V.png
    – Radek
    Apr 29, 2021 at 7:10
  • 1
    If your spreadsheet is in a locale that uses semicolons ; as formula argument separators, replace all commas , in the formula with semicolons ;, except the one in "\s*,\s*". Apr 29, 2021 at 7:13
  • OMG, you are right. Thank you for that!!!
    – Radek
    Apr 29, 2021 at 7:15
  • 1
    To get 470, use =350 * regexmatch(lower(A2), "car") + 120 * regexmatch(lower(A2), "mobile data"). Apr 29, 2021 at 8:13
  • 1
    No, I do not think that would be necessary. Apr 29, 2021 at 8:15

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.