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

[HockeyDiablo]

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

 

[HockeyDiablo]

=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"

 

[JLGWhiz]

VBA

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

 

[István Hirsch]

At me, the VBA in post #3 gives nothing in row 2, but the OP wants ", 40".

 

[JLGWhiz]

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.

 

[István Hirsch]

Or give this UDF a try:

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

 

[JLGWhiz]

This mod might fix the 40 in row 2.

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.

 

[István Hirsch]

Then the cause of the difference may have been the different (language and/or territorial) settings.

 

[JLGWhiz]

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

 

[István Hirsch]

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.