Find $ in a string of text and return numbers
 

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

--
Thanks!

Craig

Find $ in a string of text and return numbers
 

Try this:

With
Your sample data in A1:A2

B1: =--LEFT(MID(A1,SEARCH("$",A1)+1,255),SEARCH("
",MID(A1,SEARCH("$",A1)+1,255))-1)

Copy that formula down to B2

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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

--
Thanks!

Craig

Find $ in a string of text and return numbers
 

Wow! Perfect, thanks Ron!

Craig


"Ron Coderre" wrote:

Try this:

With
Your sample data in A1:A2

B1: =--LEFT(MID(A1,SEARCH("$",A1)+1,255),SEARCH("
",MID(A1,SEARCH("$",A1)+1,255))-1)

Copy that formula down to B2

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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

--
Thanks!

Craig

Find $ in a string of text and return numbers
 

The formula is working great. Is there a way to modify the formula so if the
string ends with the $ and number it will return the number?

Sample:
3. Optics Inventory €“ approved $99,999

Result:
99,999

--
Thanks!

Craig

"Craig" wrote:

Wow! Perfect, thanks Ron!

Craig


"Ron Coderre" wrote:

Try this:

With
Your sample data in A1:A2

B1: =--LEFT(MID(A1,SEARCH("$",A1)+1,255),SEARCH("
",MID(A1,SEARCH("$",A1)+1,255))-1)

Copy that formula down to B2

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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

--
Thanks!

Craig

Find $ in a string of text and return numbers
 

For text in A1 that ends in with a dollar sign followed by a number,
try this:

B1: =--MID(A1,SEARCH("$",A1)+1,255)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Craig" wrote:

The formula is working great. Is there a way to modify the formula so if the
string ends with the $ and number it will return the number?

Sample:
3. Optics Inventory €“ approved $99,999

Result:
99,999

--
Thanks!

Craig

"Craig" wrote:

Wow! Perfect, thanks Ron!

Craig


"Ron Coderre" wrote:

Try this:

With
Your sample data in A1:A2

B1: =--LEFT(MID(A1,SEARCH("$",A1)+1,255),SEARCH("
",MID(A1,SEARCH("$",A1)+1,255))-1)

Copy that formula down to B2

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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

--
Thanks!

Craig

Find $ in a string of text and return numbers
 

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