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
180
A
110 yr spring 399 rollers 188 sc 40 total 627 ck
2sc $40 radio board $140 spring $103.80 total $283.80 ck
3same day 5 yr 299 cables 58 rollers 148 sc 40 545

<tbody>
</tbody>

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

B
1188, 40
2, 40
3148, 40

<tbody>
</tbody>

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

HockeyDiablo

Board Regular
Joined
Apr 1, 2016
Messages
180
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"
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,450
Office Version
2013
Platform
Windows
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
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,450
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
May 16, 2013
Messages
1,634
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
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,450
Office Version
2013
Platform
Windows
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:

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
Then the cause of the difference may have been the different (language and/or territorial) settings.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,450
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
May 16, 2013
Messages
1,634
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.
 

Forum statistics

Threads
1,085,815
Messages
5,386,051
Members
401,980
Latest member
chaithanyakrishnagck

Some videos you may like

This Week's Hot Topics

Top