Extract from cell dollar amounts that vary in position and length

[slblanzy]

I'm trying to extract dollar amounts from a cell. The dollar amounts are all formatted with a $ but are not all the same length after the $, e.g. $100, $1235, $25, etc. Also, the dollar amounts appear in different positions in the various text strings, e.g. $100 gift certificate, Spa package for $1235, Tickets for $25 seat, etc. Is this possible?

My data looks like this (Column A is the text I'm working with, column B is the result I'm looking for):
<table>
<tbody>
<tr>
<td style="border: 1px solid #000000;">A</td>
<td style="border: 1px solid #000000;">B</td>
</tr>
<tr>
<td style="border: 1px solid #000000;">Spa package for $1235</td>
<td style="border: 1px solid #000000;">$1235</td>
</tr>
<tr>
<td style="border: 1px solid #000000;">$100 gift certificate</td>
<td style="border: 1px solid #000000;">$100</td>
</tr>
<tr>
<td style="border: 1px solid #000000;">Tickets for $25 seat</td>
<td style="border: 1px solid #000000;">$25</td>
</tr>
</tbody>
</table>

I have access to either Win 7/Excel 2007 or MacOS 10.7/Excel 2011.

 

[slblanzy]

Yes, that seems to cover at least 95% of the cells I'm working with. The rest look more like "$300 value 200 commercial spots @ $15 each" Since there are so few of these instances and the converted data is going to need to be reviewed line-by-line, I think I'm all set on this. Thanks everyone for all your help.

 

[Rick Rothstein]

Yes, that seems to cover at least 95% of the cells I'm working with. The rest look more like "$300 value 200 commercial spots @ $15 each" Since there are so few of these instances and the converted data is going to need to be reviewed line-by-line, I think I'm all set on this. Thanks everyone for all your help.

Maybe we can reduce your review time a little bit... the following formula will ignore numbers with "@ $" or "@$" in front of them.

=LOOKUP(E9+307,--LEFT(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
A1,"@$","xx"),"@ $","xxx"),"$",REPT(" ",99)),99)),ROW(INDIRECT("1:99"))))

You will have to manually remove the line feed I introduced after the last "SUBSTITUTE(" (which I did in order to not have the formula wrap at the space character).

 

[xixido]

See if this formula works for you...

=LOOKUP(E9+307,--LEFT(TRIM(RIGHT(SUBSTITUTE(A1,"$",REPT(" ",99)),99)),ROW(INDIRECT("1:99"))))

Hi Rick,
Thank you for your solution! It works like a charm for my case, however just want to expand the solution as I also have clients from England and I want to extract any $ or £ but couldn't figure out how. Just wondering if you have any solutions for that case?

 

[Rick Rothstein]

Give this formula a try...

=LOOKUP(E9+307,--LEFT(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"£","$"),"$",REPT(" ",99)),99)),ROW(INDIRECT("1:99"))))

 

[xixido]

That's awesome! Thanks so much! Just one thing if I want to keep the $ and £ symbol, is there any workaround for that?

 

[Dave Patton]

On the system that I am using at the moment, the E9+307 does not yield a large number.
I replaced that part with a Named value BigNum =9.99999999999999E+307.
Another thought is using say 10^12. I do not know if this is just my setup.

T202012c.xlsm
	A	B	C	D
1
2	Tickets for $25 seat,	25	25	25
3	Spa package for $1235	1,235	123	1,235
4	Vacation package for $12350	12,350	123	12,350
5	$100 gift certificate,	100	100	100
3b

Cell Formulas
Range	Formula
B2:B5	B2	=LOOKUP(BigNum,--LEFT(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"@$","xx"),"@ $","xxx"),"$",REPT(" ",99)),99)),ROW(INDIRECT("1:99"))))
C2:C5	C2	=LOOKUP(E10+307,--LEFT(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"@$","xx"),"@ $","xxx"),"$",REPT(" ",99)),99)),ROW(INDIRECT("1:99"))))
D2:D5	D2	=LOOKUP(10^12,--LEFT(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"@$","xx"),"@ $","xxx"),"$",REPT(" ",99)),99)),ROW(INDIRECT("1:99"))))

 

[Rick Rothstein]

Maybe this?

=IF(ISNUMBER(FIND("$",A1)),"$","£")&LOOKUP(E9+307,--LEFT(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"£","$"),"$",REPT(" ",99)),99)),ROW(INDIRECT("1:99"))))

 

[xixido]

Maybe this?

=IF(ISNUMBER(FIND("$",A1)),"$","£")&LOOKUP(E9+307,--LEFT(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"£","$"),"$",REPT(" ",99)),99)),ROW(INDIRECT("1:99"))))

Awesome! Thank you so much !