Re: Extracting a number from a strig

2050 1
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi all
I am trying to convert strings with numbers in them to just numbers.
Screen Shot 2022-09-11 at 8.34.02 PM
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

6 Replies 6

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})

5 - Automation Enthusiast
5 - Automation Enthusiast

I tried that but it gives me Nan
Screen Shot 2022-09-11 at 8.51.30 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.

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.

5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you I was able to do it with a REGEX formula


What REGEX formula did you end up using?