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

HockeyDiablo · Jul 13, 2016

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]

NeonRedSharpie · Jul 13, 2016

Re: Extract each set of numbers to a column?

MickG said:
"." ???

This is to essentially remove numbers after the decimal is they are "00", and include them in the same cell if they are "38". Otherwise my code would count 100.00 as being two strings of numbers. "100" and "00".

JoeMo · Jul 13, 2016

Re: Extract each set of numbers to a column?

Here's a UDF array formula you can try that extracts numbers to individual cells. Select as many cells as you think you will ever need, enter the formula and confirm with ctrl + shift + enter. In the example below I selected B2:I2 for the string in A2. The #N/A error indicates no more numbers to extract.

Excel Workbook

A

B

C

D

E

F

G

H

I

1

1st

2nd

3rd

4th

5th

6th

7th

8th

2

Steel White $599 Sears Craftsman Trolley 22 198 galv rollers Total$815

599

22

198

815

#N/A

3

5 yr Sp $130.00 cables 38 Keyless entry FREE LM Belt Drive 10yr War. $349.00 517 Total on CC

5

130.00

38

10

349.00

517

#N/A

ExtractNumsToCells (2)

Code:

Function ExtractNumsToCells(ByVal S As String) As Variant
Dim Patt As Variant, Vout
Patt = "[0-9]+\.?\d{0,2}"
If S = "" Then
    ExtractNumsToCells = ""
    Exit Function
End If
With CreateObject("VBScript.regexp")
    .Global = True
    .Pattern = Patt
    If .test(S) Then
        For Each Match In .Execute(S)
            Vout = Vout & " " & Match
        Next Match
    End If
    ExtractNumsToCells = Split(Right(Vout, Len(Vout) - 1), " ")
End With
End Function

HockeyDiablo · Jul 13, 2016

Re: Extract each set of numbers to a column?

The decimals are not very important to be honest. I can clean them up as all of my dollar figures have .00

Just replace .00 with ""

HockeyDiablo · Jul 13, 2016

Re: Extract each set of numbers to a column?

JoeMo said:
Here's a UDF array formula you can try that extracts numbers to individual cells. Select as many cells as you think you will ever need, enter the formula and confirm with ctrl + shift + enter. In the example below I selected B2:I2 for the string in A2. The #N/A error indicates no more numbers to extract.

ExtractNumsToCells (2)

A B C D E F G H I
1st 2nd 3rd 4th 5th 6th 7th 8th
Steel White $599 Sears Craftsman Trolley 22 198 galv rollers Total$815 #N/A #N/A #N/A #N/A
5 yr Sp $130.00 cables 38 Keyless entry FREE LM Belt Drive 10yr War. $349.00 517 Total on CC #N/A #N/A

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:581px;"><col style="width:28px;"><col style="width:46px;"><col style="width:28px;"><col style="width:28px;"><col style="width:46px;"><col style="width:39px;"><col style="width:39px;"><col style="width:39px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]599[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]198[/TD]
[TD="align: right"]815[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]130.00[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]349.00[/TD]
[TD="align: right"]517[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula
B2 {=ExtractNumsToCells(A2)}

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Code:

Function ExtractNumsToCells(ByVal S As String) As Variant Dim Patt As Variant, Vout Patt = "[0-9]+\.?\d{0,2}" If S = "" Then ExtractNumsToCells = "" Exit Function End If With CreateObject("VBScript.regexp") .Global = True .Pattern = Patt If .test(S) Then For Each Match In .Execute(S) Vout = Vout & " " & Match Next Match End If ExtractNumsToCells = Split(Right(Vout, Len(Vout) - 1), " ") End With End Function

I get nothing on a CTRL:+SHIFT+ENTER

JoeMo · Jul 13, 2016

Re: Extract each set of numbers to a column?

HockeyDiablo said:
I get nothing on a CTRL:+SHIFT+ENTER

Then you didn't enter the formula and/or the code correctly. Using the example I posted for extraction from the string in A2 do the following:
1. Select cell B2 with your mouse.
2. Hold down the left mouse key and continue the selection by moving the mouse right until the range B2:I2 is selected, with B2 active.
3. Type: =ExtractNumsToCells(A2) - you should see this function in the list of possible functions provided. If not you haven't put the code in the right place.
4. Press Ctrl+Shift+Enter down together.

Here's how to put the code in your workbook:
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. 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).
4. Make sure you have enabled macros whenever you open the file or the code will not run.
And if you prefer to not have the .00 in the output, here's a revised code to handle that:

Code:

Function ExtractNumsToCells(ByVal S As String) As Variant
Dim Patt As Variant, Vout
Patt = "[0-9]+\.?\d{0,2}"
If S = "" Then
    ExtractNumsToCells = ""
    Exit Function
End If
With CreateObject("VBScript.regexp")
    .Global = True
    .Pattern = Patt
    If .test(S) Then
        For Each Match In .Execute(S)
            Vout = Vout & " " & Format(Match, "0")
        Next Match
    End If
    ExtractNumsToCells = Split(Right(Vout, Len(Vout) - 1), " ")
End With
End Function

HockeyDiablo · Jul 13, 2016

Re: Extract each set of numbers to a column?

JoeMo said:
Then you didn't enter the formula and/or the code correctly. Using the example I posted for extraction from the string in A2 do the following:
1. Select cell B2 with your mouse.
2. Hold down the left mouse key and continue the selection by moving the mouse right until the range B2:I2 is selected, with B2 active.
3. Type: =ExtractNumsToCells(A2) - you should see this function in the list of possible functions provided. If not you haven't put the code in the right place.
4. Press Ctrl+Shift+Enter down together.

Here's how to put the code in your workbook:
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. 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).
4. Make sure you have enabled macros whenever you open the file or the code will not run.
And if you prefer to not have the .00 in the output, here's a revised code to handle that:

Code:

Function ExtractNumsToCells(ByVal S As String) As Variant Dim Patt As Variant, Vout Patt = "[0-9]+\.?\d{0,2}" If S = "" Then ExtractNumsToCells = "" Exit Function End If With CreateObject("VBScript.regexp") .Global = True .Pattern = Patt If .test(S) Then For Each Match In .Execute(S) Vout = Vout & " " & Format(Match, "0") Next Match End If ExtractNumsToCells = Split(Right(Vout, Len(Vout) - 1), " ") End With End Function

still no dice...

http://www.screencast.com/t/njXO3fMj2R

JoeMo · Jul 13, 2016

Re: Extract each set of numbers to a column?

HockeyDiablo said:
still no dice...

07.13.2016-12.40.53 - JamStr8's library

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.

HockeyDiablo · Jul 13, 2016

Re: Extract each set of numbers to a column?

JoeMo said:
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.

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

Rick Rothstein · Jul 13, 2016

Re: Extract each set of numbers to a column?

Does this macro do what you want...

Code:

[table="width: 500"]
[tr]
	[td]Sub ExtractAllNumbers()
  Dim R As Long, X As Long, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X, 1) Like "[!0-9.$]" Then Mid(Data(R, 1), X) = " "
    Next
    Data(R, 1) = Application.Trim(Replace(Replace(Data(R, 1), " .", " "), ". ", " "))
  Next
  Range("B1").Resize(UBound(Data)) = Data
  Columns("B").TextToColumns , xlDelimited, , , 0, 0, 0, 1, 0
End Sub[/td]
[/tr]
[/table]

HockeyDiablo · Jul 13, 2016

Re: Extract each set of numbers to a column?

Rick Rothstein said:

Does this macro do what you want...

Code:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ExtractAllNumbers()
  Dim R As Long, X As Long, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X, 1) Like "[!0-9.$]" Then Mid(Data(R, 1), X) = " "
    Next
    Data(R, 1) = Application.Trim(Replace(Replace(Data(R, 1), " .", " "), ". ", " "))
  Next
  Range("B1").Resize(UBound(Data)) = Data
  Columns("B").TextToColumns , xlDelimited, , , 0, 0, 0, 1, 0
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Is there anything this guy cant figure out?? Amazing...

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

HockeyDiablo

Board Regular

NeonRedSharpie

Well-known Member

Excel Facts

JoeMo

MrExcel MVP

HockeyDiablo

Board Regular

HockeyDiablo

Board Regular

JoeMo

MrExcel MVP

HockeyDiablo

Board Regular

JoeMo

MrExcel MVP

HockeyDiablo

Board Regular

Rick Rothstein

MrExcel MVP

HockeyDiablo

Board Regular

Similar threads

Forum statistics

Share this page

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

Board Regular

Well-known Member

Excel Facts

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock