Need Help Extracting Price from Text Strings of Various Lengths

[marduino]

Hello again forum,

I am having trouble extracting just the price values from the rows below. I keep running into issues since the "." is not a unique character in some rows. I appreciate any help!

CHART.xlsx
	A
1	3Jun21 3CD2 5 Charlie'sarchangel (Ortiz, Jr., Irad) 122 L b 5 3 31 1/2 32 11 13 1Neck 1.95* 3-2p trns,drift out1/8
2	29Jun21 7PRX5 8 Ekhtibaar (Santana, Jr., Ricardo) 122 L b 7 6 51/2 51 1/2 3Head 22 26 3/4 18.80 2-3w2nd,mvd out,gained
3	3Jun21 3CD1 4 Wagon Boss (Rosario, Joel) 126 L b 4 1 21/2 21/2 21 34 33 1/4 3.00 2path,ins3/8,tired
4	8May21 1BEL7 6 Flowers for Lisa (Saez, Luis) 124 L b 6 4 41 4Head 51 1/2 41 1/2 410 3.35 4wide turns,empty
5	20Jun21 6BEL8 2 Playwright (Cardenas, Luis) 122 L 2 5 8 8 73 1/2 61 1/2 5Neck 24.25 stumbled brk,bumped
6	12Jun21 3BEL4 9 Hammerin Aamer (Cohen, David) 124 L b 8 7 6Head 61 1/2 62 1/2 53 1/2 612 3.85 3w turns,no response
7	10Jul21 2BEL9 3 No Lime (Hernandez Moreno, Omar) 114 L f 3 8 72 1/2 72 8 8 710 16.40 bmpd brk,bobbld,stdy
8	2Jul21 4BEL5 1 Revenio (Gaffalione, Tyler) 122 L b 1 2 11 11/2 41 1/2 78 8 35.75 ins,headed1/2,stopped
9	--- 5 Outfoxed (Alvarado, Junior) 119 - - 4 7 71/2 61/2 44 1/2 31 15.10 off 1/2 step slw,3-4w
10	--- 8 Li Li Bear (Davis, Dylan) 119 - - 7 3 32 32 31/2 43 1/2 14.30 outside,3w turn,tired
Sheet3

 

[Micron]

I'm a bit proficient with Access code but not so much with Excel so maybe this won't help if certain functions and methods have no Excel equivalent.
If that were Access data I'd probably loop through each character and test for numeric. If true, add it to a string variable. If during the loop it encounters a non numeric character, dump the variable value and start again, unless the variable contains a dot/period. If variable contains a . and encounters another non-numeric, exit the loop. I suppose you could test if the final result is numeric and do something since IsNumeric(1.a25) should raise an error you can trap. However, my thinking is that it should not happen.
All that is off the top of my head so once you get started coding it's likely that other factors will arise.
HTH

 

[Micron]

Well, I'm often a patsy for a coding challenge so I came up with something that seems to work based on the sample data. Note that it requires that the price always ends in 2 digits after the decimal as was shown. If $4.00 is ever represented as $4 then it won't work. It's late here and I have to quit so will post what I just ended up with. No doubt there are better ways to deal with the Excel part (especially since this code assumes the first cell is A1). The point of the exercise was not to make a slick procedure that would properly deal with spreadsheet stuff. The point was to figure out the looping logic. The output is a simple debug.print - it would be up to the user to figure out what to do with it.

Sub ExtractPrice()
Dim strIn As String, strOut As String, strTest As String
Dim i As Integer, lrow As Integer, c As Integer

lrow = Range("A1", Range("A1").End(xlDown)).Rows.Count
i = 1

Range("A1").Select
For c = 1 To lrow
   strIn = Sheet1.Range("A" & c)
   For i = 1 To Len(strIn)
      strTest = Mid(strIn, i, 1)
      If IsNumeric(strTest) Then
         strOut = strOut & strTest
         'Debug.Print strOut
         If Len(strOut) - Len(CStr(Int(strOut))) = 3 Then
            Debug.Print "Price is " & strOut
            strOut = ""
            Exit For
         End If
      ElseIf strTest = "." Then strOut = strOut & strTest
      Else: strOut = ""
      End If
   Next
Next

End Sub