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>

 

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

	A	B	C	D	E	F	G
1	Steel White $599 Sears Craftsman Trolley 22 198 galv rollers Total$815	599	22	198	815
2	5 yr Sp $130.00 cables 38 Keyless entry FREE LM Belt Drive 10yr War. $349.00 517 Total on CC	5	130	38	10	349	517

<tbody>
</tbody>

 

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