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.

 

[Jonmo1]

try

=MID(A2,FIND("$",A2),FIND(" ",A2&" ",FIND("$",A2))-FIND("$",A2))

If you need that text result to be converted to a number, just add 0
=MID(A2,FIND("$",A2),FIND(" ",A2&" ",FIND("$",A2))-FIND("$",A2))+0

 

[Rick Rothstein]

Give this formula a try...

=MID(A2,FIND("$",A2),FIND(" ",A2&" ",FIND("$",A2))-FIND("$",A2))

 

[vds1]

VBA method

Sub Testt()
     
    Dim RegX As Object
    Dim Rng As Range
     
    Set RegX = CreateObject("vbscript.regexp")
     
    With RegX
        .Global = True
        .Pattern = "[^-0-9-$]"
    End With
     
    For Each Rng In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        Rng.Offset(, 1) = RegX.Replace(Rng, "")
    Next Rng
     
End Sub

 

[schielrn]

ADVERTISEMENT

This is one way:

Excel Workbook
	A	B
1	Spa package for $1235	$1235
2	$100 gift certificate	$100
3	Tickets for $25 seat	$25
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(MID(A1,FIND("$",A1),IFERROR(FIND(" ",MID(A1,FIND("$",A1),LEN(A1))),LEN(A1))))

Hope that helps.

 

[slblanzy]

Thanks everyone for replying. Jonmo1's formula, =MID(A2,FIND("$",A2),FIND(" ",A2&" ",FIND("$",A2))-FIND("$",A2))+0, was exactly what I needed.

 

[Rick Rothstein]

ADVERTISEMENT

VBA method

Sub Testt()
     
    Dim RegX As Object
    Dim Rng As Range
     
    Set RegX = CreateObject("vbscript.regexp")
     
    With RegX
        .Global = True
        .Pattern = "[^-0-9-$]"
    End With
     
    For Each Rng In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        Rng.Offset(, 1) = RegX.Replace(Rng, "")
    Next Rng
     
End Sub

A (really) different VBA method...

Sub Testt()
  Dim Addr As String
  Addr = "A2:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr).Offset(, 1) = Evaluate(Replace("IF(LEN(@),MID(@,FIND(""$"",@),FIND("" ""," & _
                                     "@&"" "",FIND(""$"",@))-FIND(""$"",@)))", "@", Addr))
End Sub

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[slblanzy]

Looks like I spoke a little too soon. After reviewing the results of Jonmo1's formula I realized I didn't pass along all the info I should have. Sorry about that. I only looked at a random sample of the text cells at first but now see that there are more of them with the below string where the dollar amount I'm looking for is between the "$" and "value" (again column A is the text I'm working with, column B is the result I'm looking for):
<p></p>
<table border="0">
<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;">3 $10 gift certificates ($30 value)</td>
<td style="border: 1px solid #000000;">$30</td>
</tr>
<tr>
<td style="border: 1px solid #000000;">10 $15 gift certificates ($150 value)</td>
<td style="border: 1px solid #000000;">$150</td>
</tr>
<tr>
<td style="border: 1px solid #000000;">200 commercial spots @ $15 each - $300 value</td>
<td style="border: 1px solid #000000;">$300</td>
</tr>
</tbody>
</table>

Yes, those cells are really copied directly from my data, new math and all. How they were using this to track and report on anything is a real head scratcher.

 

[Rick Rothstein]

See if this formula works for you...

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