Is there a better way to extract data from a string?

[HockeyDiablo]

I am trying to extract random pricing data within a string. I have about 50 set prices for items within the string. Wondering if one of you excel geniuses on here can think of an easy solution? Here is the method I have came up with. Any ideas are welcome.

A	B	C	D	E
	Description	Left 10 Chars	Number Extracted	Right 10 Chars
1	Steel White $599 Sears Craftsman Trolley 22 198 galv rollers Total $815	eel White	599	Sears Cra
2	5 yr Sp $130.00 cables 38 Keyless entry FREE LM Belt Drive 10yr War. $349.00 517 Total on CC	5 yr Sp $	130	cables 38

<tbody>
</tbody>

 

[HockeyDiablo]

Re: Extract each set of numbers to a column?

My oversight, I gave you the wrong set of instructions for installation of the code.

Delete the code from the sheet (right-click the sheet tab and select View Code to get to it). Then follow these steps:

To install the code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Press Alt+F8 keys to run the code
7. Make sure you have enabled macros whenever you open the file or the code will not run.

It does work now. Nice work

 

[JoeMo]

Re: Extract each set of numbers to a column?

I initially had it there when I originally tested and received null results. ALT+F8 doesnt even bring up the function labelled [ExtractNumsToCells]

What do you mean by "null" results. What did you see in the cells that the formula was in?

 

[Rick Rothstein]

Re: Extract each set of numbers to a column?

Is there anything this guy cant figure out??

Thanks for the nice words, but you are being far too kind... there is lots that I cannot figure out, you just don't see me post in response to them.

 

[JoeMo]

Re: Extract each set of numbers to a column?

It does work now. Nice work

OK - ignore post #22 which was posted before I saw this. Glad you got it working.

 

[HockeyDiablo]

Re: Extract each set of numbers to a column?

Thanks for the nice words, but you are being far too kind... there is lots that I cannot figure out, you just don't see me post in response to them.

You're quite welcome, you have came through for me quite a few times and for that I thank you.

 

[HockeyDiablo]

Re: Extract each set of numbers to a column?

OK - ignore post #22 which was posted before I saw this. Glad you got it working.

no worries, thanks for the leg work. This stuff is as complicated as it is entertaining. Thanks again

 

[HockeyDiablo]

Re: Extract each set of numbers to a column?

Thanks for the nice words, but you are being far too kind... there is lots that I cannot figure out, you just don't see me post in response to them.

What characters would create a type mismatch? I tried to debug and locate where the mismatch was and having no luck.

I copy and paste sections of the data and it works great. But when I run the full 97k rows, I get the mismatch

 

[snb_]

Re: Extract each set of numbers to a column?

I was thinking about this UDF:

Function F_snb(c00)
    F_snb = ""

    If c00 <> "" Then
       st = Split(c00)
       For j = 0 To UBound(st)
          If Val(Replace(st(j), "$", "")) = 0 Then st(j) = ""
       Next
       F_snb = Join(st)
    End If
End Function

or

Function F_snb(c00)
    F_snb = ""
    
    If c00 <> "" Then
       st = Split(Replace(c00, "$", ""))
       For j = 0 To UBound(st)
          If Val(st(j)) = 0 Then st(j) = "~"
       Next
       F_snb = Join(Filter(st, "~", 0))
    End If
End Function

 

[HockeyDiablo]

Re: Extract each set of numbers to a column?

What characters would create a type mismatch? I tried to debug and locate where the mismatch was and having no luck.

I copy and paste sections of the data and it works great. But when I run the full 97k rows, I get the mismatch

found it, dang #NAME? error I didnt catch