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
  • Please show what you tried and add a brief description of your search efforts as is suggested in How to Ask. – Rubén Apr 28 at 18:32
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 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*". – doubleunary Apr 29 at 7:13
  • OMG, you are right. Thank you for that!!! – Radek Apr 29 at 7:15
  • 1
    To get 470, use =350 * regexmatch(lower(A2), "car") + 120 * regexmatch(lower(A2), "mobile data"). – doubleunary Apr 29 at 8:13
  • 1
    No, I do not think that would be necessary. – doubleunary Apr 29 at 8:15

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.