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.



[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]

 

[bhos123]

=MID(A19,1+FIND("$",A19,1),FIND(" ",MID(A19,FIND("$",A19,1),LEN(A19)))-2)

Assuming you have text in A19

 

[HockeyDiablo]

=MID(A19,1+FIND("$",A19,1),FIND(" ",MID(A19,FIND("$",A19,1),LEN(A19)))-2)

Assuming you have text in A19

That will work great where the user incorporated the $, what about the ones without?

 

[HockeyDiablo]

Extract each set of numbers to a column?

Is there a way to extract each and every numbering sequence in a string to its own individual column?






[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Steel White $599 Sears Craftsman Trolley 22 198 galv rollers Total$815[/TD]
[TD]599[/TD]
[TD]22[/TD]
[TD]198[/TD]
[TD]815[/TD]
[TD][/TD]
[TD][/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[/TD]
[TD]130[/TD]
[TD]38[/TD]
[TD]10[/TD]
[TD]349[/TD]
[TD]517[/TD]
[/TR]
</tbody>[/TABLE]

 

[bhos123]

Not very easy, becoz you have 599 22 198 815 four different values in the range.

 

[NeonRedSharpie]

Re: Extract each set of numbers to a column?

Sub extractNumbers()

    Dim lastNum As Boolean
    Dim thisNum As Boolean
    Dim nextCol As Integer
    
    nextCol = 2


    For x = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        For y = 1 To Len(Cells(x, 1).Value)
            Select Case IsNumeric(Mid(Cells(x, 1).Value, y, 1))
                Case True
                    Debug.Print Mid(Cells(x, 1).Value, y, 1)
                    printVal = printVal & Mid(Cells(x, 1).Value, y, 1)
                    thisNum = True
                Case False
                    lastNum = thisNum
                    thisNum = False
            End Select
            
            If Mid(Cells(x, 1).Value, y, 1) = "." And lastNum = True Then
                thisNum = True
                printVal = printVal & Mid(Cells(x, 1).Value, y, 1)
            End If
            
            If thisNum = False And lastNum = True Then
                Cells(x, nextCol) = printVal
                nextCol = nextCol + 1
                printVal = ""
            End If
        Next y
        nextCol = 2
        printVal = ""
        lastNum = False
        thisNum = False
    Next x


End Sub

This worked for the sample data. Might need some more rigorous testing though. I had to add a bit of a workaround for periods in the data.

 

[MickG]

Re: Extract each set of numbers to a column?

"." ???

 

[Joe4]

Re: Extract each set of numbers to a column?

I have merged your two threads together.

In the future, please continue in the same thread (do not start a new thread on the same topic). Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: Forum Rules).

 

[HockeyDiablo]

Re: Extract each set of numbers to a column?

I have merged your two threads together.

In the future, please continue in the same thread (do not start a new thread on the same topic). Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: Forum Rules).

I believe one posting was in relation to extracting data 10 characters to the left and right of a numbered value;
and the other was simply how to extract numbers in a sequence from a string.

Could you please inform me of how those 2 posts are the same?

 

[bhos123]

Re: Extract each set of numbers to a column?

For left 10 chars and right 10 chars, you can use =Left(A2,10) and =Right(A2,10) functions.