Sep 11, 2022 05:36 PM
Hi all
I am trying to convert strings with numbers in them to just numbers.
So the string is in that form R.N.A.(335,000) I just want the number
The number ranges from 0 to 10,000,000
Thank you in advance
Sep 11, 2022 05:44 PM
You could try creating a formula like this, but note that this is not foolproof so you will need to spot-check to make sure that it works all the time:
VALUE({Buyer Name copy})
Sep 11, 2022 05:51 PM
I tried that but it gives me Nan
Sep 11, 2022 06:16 PM
Ah yeah, that’s a bummer.
You could make a more elaborate conditional formula to account for those entries using an IF() statement to check for the presence of those parentheses, and then use MID() function to extract the number out of there.
But I’m not sure how many other exceptions you would have to account for, so it might not be worth it to go down that path.
It might be best to just find a REGEX function to do this. I’m sure someone will chime in with a REGEX function, or you can probably find one in the forums from past threads.
Sep 11, 2022 09:38 PM
I had to start learning REGEX for my current task, because otherwise formulas will be very large.
Regex to extract first number in string is REGEX_EXTRACT({Field},‘\d{1,5}’), where 1,5 - min/max possible number of digits.
I guess it can be done in a more effective way, but as “regex beginner” I would use REGEX_EXTRACT(
SUBSTITUTE({Buyer name copy},‘,’,‘’)
,‘\d{1,8}’) , wrapping into ISERROR() to avoid error messages.
Sep 12, 2022 08:29 AM
Thank you I was able to do it with a REGEX formula
Sep 12, 2022 08:43 AM
What REGEX formula did you end up using?