On Wed, 13 Sep 2006 08:10:02 -0700, Craig
wrote:
In a string of text I need to find the first occurrence of a $ (there will
only be one $ in each string) and then return the numbers after the dollar
sign until the first space.
Samples:
1.) E-100 Inventory – approved $42,000 (bc 9.32)
2.) E-1700 Inventory – signage $7,000 September 2006
Results:
42,000
7,000
One way:
Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr
The use the Regular Expression formula:
=REGEX.MID(A1,"(?<=\$).*?(?=\s|$)")
That will return a text string of 42,000.
If you want the value to be numeric, prefix the formula with a double unary:
=--REGEX.MID(A1,"(?<=\$).*?(?=\s|$)")
--ron