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

HockeyDiablo

Board Regular
Joined
Apr 1, 2016
Messages
182
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.



[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]Description[/TD]
[TD]Left 10 Chars[/TD]
[TD]Number Extracted[/TD]
[TD]Right 10 Chars[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Steel White $599 Sears Craftsman Trolley 22 198 galv rollers Total $815[/TD]
[TD]eel White[/TD]
[TD]599[/TD]
[TD] Sears Cra[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5 yr Sp $130.00 cables 38 Keyless entry FREE LM Belt Drive 10yr War. $349.00 517 Total on CC[/TD]
[TD]5 yr Sp $[/TD]
[TD]130[/TD]
[TD] cables 38[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
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
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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?
 
Upvote 0
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.:laugh:

You're quite welcome, you have came through for me quite a few times and for that I thank you.
 
Upvote 0
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.:laugh:


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
 
Upvote 0
Re: Extract each set of numbers to a column?

I was thinking about this UDF:

Code:
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
Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top