Extract first numerical value to the left & right of a defined criteria from a string of text

HockeyDiablo

Board Regular
Joined
Apr 1, 2016
Messages
182
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]10 yr spring 399 rollers 188 sc 40 total 627 ck[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]sc $40 radio board $140 spring $103.80 total $283.80 ck [/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]same day 5 yr 299 cables 58 rollers 148 sc 40 545[/TD]
[/TR]
</tbody>[/TABLE]

Above are a few samples strings from Microsoft Excel that I need the extraction from, respectively in cells "A1", "A2", "A3"...

I have several items in the string and need to extract the pricing, but it could be to the left or the right and wont always carry a $ sign.

Example: I would like to select "sc" as the defined criteria and return the first numerical value to the left and right


Return: something like this

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]188, 40[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"], 40[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]148, 40[/TD]
[/TR]
</tbody>[/TABLE]

I could work with values in "B" and "C"
 
Last edited:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Code:
=IFERROR(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,SEARCH("sc ",A1)-2)," ",REPT(" ",LEN(LEFT(A1,SEARCH("sc ",A1)-2)))),LEN(LEFT(A1,SEARCH("sc ",A1)-2)))),"$",""),"")

Works for each side "respective Right/Left" , but is there a VBA solution or perhaps one with RegExp? "Also, does anyone know of a good resource for learning RegExp, PM me if you have one. I would enjoy learning that"
 
Upvote 0
VBA

Code:
Sub t()
Dim rng As Range, c As Range, ary As Variant
    With ActiveSheet
        Set rng = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
    End With
    For Each c In rng
        ary = Split(c.Value, " ")
            For i = LBound(ary) To UBound(ary)
                If ary(i) = "sc" Then
                    If i > 0 Then
                        If IsNumeric(ary(i - 1)) Then
                            c.Offset(, 1) = ary(i - 1)
                        End If
                    End If
                    If IsNumeric(ary(i + 1)) Then
                        c.Offset(, 2) = ary(i + 1)
                    End If
                    Exit For
                End If
            Next
    Next
End Sub
 
Upvote 0
Using the example data in the OP, I get the results in the OP, except the $ symbols also appear in the results. Don't know what the difference is in the data except that the delimiter of one space could be different in your data than in that of the example.
 
Upvote 0
Or give this UDF a try:

Code:
Function ArSc(s As String) As String
 s = Replace(s, "$", "")
 With CreateObject("VBScript.Regexp")
    .Pattern = ".*?([0-9]+)*[ ]*sc ([0-9]+).*"
    If .test(s) Then ArSc = .Replace(s, "$1, $2")
 End With
End Function
 
Upvote 0
This mod might fix the 40 in row 2.
Code:
Sub t()
Dim rng As Range, c As Range, ary As Variant
    With ActiveSheet
        Set rng = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
    End With
    For Each c In rng
        ary = Split(c.Value, " ")
            For i = LBound(ary) To UBound(ary)
                If ary(i) = "sc" Then
                    If i > LBound(ary) Then
                        If IsNumeric(ary(i - 1)) Then
                            c.Offset(, 1) = ary(i - 1)
                        End If
                    End If
                    If IsNumeric(ary(i + 1)) Then
                        c.Offset(, 2) = ary(i + 1)
                    End If
                    Exit For
                End If
            Next
    Next
End Sub
Unless it is not seeing the $ symbol as numeric. But it did return the 40 with the $ symbol in my test.
 
Last edited:
Upvote 0
Then the cause of the difference may have been the different (language and/or territorial) settings.

All that I changed was the compared value of the first item in the array created by the Split function. By using LBound instead of zero, it allows for the difference in base values of either 0 or 1, whichever the array uses. Mine was using zero when I wrote the original code so I used zero in the code. If for some reason yours was using 1 as the base number, then it would have given different results on that particular line of code. Although it should have been the same as mine, I have learned that things are not always as one expects them to be in VBA code writing.
Regards, JLG
 
Upvote 0
All that I changed was the compared value of the first item in the array created by the Split function. By using LBound instead of zero, it allows for the difference in base values of either 0 or 1, whichever the array uses. Mine was using zero when I wrote the original code so I used zero in the code. If for some reason yours was using 1 as the base number, then it would have given different results on that particular line of code. Although it should have been the same as mine, I have learned that things are not always as one expects them to be in VBA code writing.
Regards, JLG

Sorry, in my former post I have forgotten to quote your first code. Now checked your code in post #7 and no data in row 2 either.

I think it is because of the different settings, namely in row 2 "$40" is not recognized in your code by my Excel as "Isnumeric" but I think in your Excel is. If I change "$40" to, for example, "540" it works at me too.
 
Upvote 0

Forum statistics

Threads
1,224,559
Messages
6,179,513
Members
452,921
Latest member
BBQKING

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