doctornick0
asked on
Extract first value (number) from strings in cells in Excel
Hi, everybody!
I have an annoying challenge where I have Excel data like
"Projection packages range from $200-$350" or
"$31.22 per user ($1,561 flat rate) Dedicated Bandwidth for 50" or
"12% added to F&B, 10% added to all meeting room rental charges"
in cells in worksheets containing property information, and I'd like to try to create a UDF that will pull out the 1st instance of a number out of the string in these cells (or, alternatively, if a UDF is not possible, just a regular sub).
Thus, something like "Projection packages range from $200-$350" would convert to 200,
"$31.22 per user ($1,561 flat rate) Dedicated Bandwidth for 50" would convert to 31.22
"12% added to F&B, 10% added to all meeting room rental charges" would convert to 12
Thanks for any help you can provide.
Best,
-doctornick0
I have an annoying challenge where I have Excel data like
"Projection packages range from $200-$350" or
"$31.22 per user ($1,561 flat rate) Dedicated Bandwidth for 50" or
"12% added to F&B, 10% added to all meeting room rental charges"
in cells in worksheets containing property information, and I'd like to try to create a UDF that will pull out the 1st instance of a number out of the string in these cells (or, alternatively, if a UDF is not possible, just a regular sub).
Thus, something like "Projection packages range from $200-$350" would convert to 200,
"$31.22 per user ($1,561 flat rate) Dedicated Bandwidth for 50" would convert to 31.22
"12% added to F&B, 10% added to all meeting room rental charges" would convert to 12
Thanks for any help you can provide.
Best,
-doctornick0
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I missed the decimal places. My bad.
The following pattern will also do the trick...
The following pattern will also do the trick...
"\d+\.?\d+"
FirstNumber-v2.xlsm
@doctornick0
Did you test the solution before accepting it?
Please find the attached with the comparison between the two patterns and see what both patterns return for the same strings.
If you are happy with the output returned by the accepted solution, no issues. :)
FirstNumber_Test.xlsm
Did you test the solution before accepting it?
Please find the attached with the comparison between the two patterns and see what both patterns return for the same strings.
If you are happy with the output returned by the accepted solution, no issues. :)
FirstNumber_Test.xlsm
Open in new window
Then assuming your string is in A2, you can try the UDF like below...Open in new window
FirstNumber.xlsm