Excel VBA Extract Specific Data from Column of Mixed Data

Question

I have a list of Tyres form the internet, the list is 5,000 lines long in one column. I need to extract from each line the data in BOLD ideally into the next column

EXAMPLE of TYRES

LS388 (145/70 R13 71T)
LS388 (155/65 R13 73T)
LS388 (155/65 R14 75T)
4-Seasons (155/65 R14 75T)
CT6 (155/70 R12 104N) 72EE
LS388 (155/70 R13 75T)

The problem is that the number can be between 59 and 120 and the letter could be H T V R N X Z and so on. Also the text could be anywhere within the line of data not always towards the end as shown.

There could be 100 variations to look for and

Rather than having one line of code to search for a LIKE 71T for each line of tyres, can I use a source table of these variations and reference them one by one in the code is some sort of loop? or other suggestions if in VBA appreciated

At the moment I have this VBA code for each possible variation, one line for each variant.

ElseIf ActiveCell.Value Like "*79S*" Then
ActiveCell.offset(0,1).Value = "79S"

I can see that it is a string (from right to left) starting from a ) and ending at a space. Is it true in all cases — usmanhaq, Oct 17 '18 at 12:13
"and so on" - what does this mean? Why not 72EE in the penultimate example? — SJR, Oct 17 '18 at 12:15
Unfortunatley the data can appear almost in any order begining with numbers or letters, also the data I want (in Bold) might or might not be in Brackets. Humans build the data so can be extremely inconsistant. — David, Oct 17 '18 at 12:27
AND SO ON. well the data in this example is within the brackets, though not always the case, the 72EE refers to something totally different, The issue is I need to look, for example, for 70T 71T 72T 73T 74T 75T (approx 100 different versions) and find out which one is in each line for around 5,000 lines. — David, Oct 17 '18 at 12:31
If there is no logic or pattern to the string you want to extract, it's a tough one. You could have a list of all the codes you want, and loop through each of them for each entry in your data. You might get false positives I guess, but I think that's the best you can do. — SJR, Oct 17 '18 at 12:36

score 1 · Answer 1 · edited Oct 17 '18 at 12:42

1

Insert this formula in a cell it is assuming your string is present in column A, you can change it if it is not the case and check how many it extracts.

=MID(A1,SEARCH(" ",A1,SEARCH("R1?",A1))+1,SEARCH(")",A1)-SEARCH(" ",A1,SEARCH("R1?",A1))-1)

filter out the remaining ones, find some thing common in them and let me know and we can build another formula for those cells.

edited Oct 17 '18 at 12:42

Pᴇʜ

56,719
10
49
73

answered Oct 17 '18 at 12:39

usmanhaq

1,527
1
6
11

Pᴇʜ · Answer 2 · 2018-10-17T12:47:42.263

I recommend to use Regular Expressions for that if you need to do it with VBA. There is a pretty good explanation at How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops.

As pattern you could use something like .+\(.+ (.+)\).* (see https://regex101.com/r/jK1zKc/1/)

For a manual solution
Use Split text into different columns with the Convert Text to Columns Wizard to split into columns by the spaces.

Then do a simple replace ")" by "" in column D.

Or do the manual solution with VBA (assuming your data in column A):

Option Explicit

Sub SplitAndDelet()
    Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:= _
        ")", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)) _
        , TrailingMinusNumbers:=True
    Range("A:C,E:E").Delete Shift:=xlToLeft
End Sub

James Cooke · Answer 3 · 2018-10-17T14:32:02.470

If you want to do this in vba you could set up an array of tyres and loop through them for each cell. for example if you had this on your sheet;

you could do something like this;

Public Sub FindTyres()

' Column to Loop
Dim col As String
col = "B"

' rows to Loop
Dim startRow As String
Dim endRow As String
startRow = "2"
endRow = "7"

' Get list of Tyres
Dim tyresArr()
tyresArr = getTyresArr()

' Set Range to Loop
Dim rng As Range, cell As Range
Set rng = Range(col & startRow & ":" & col & endRow)

' Looping through Array params
Dim tyre As Variant

' Loop through Cells
    For Each cell In rng
       currentCellVal = cell.Value

       ' Loop through tyres
       For Each tyre In tyresArr

            Debug.Print tyre
            ' if you find it do something
             If InStr(1, currentCellVal, CStr(tyre)) <> 0 Then
                 MsgBox "Value " & CStr(tyre) & " Contained in Cell " & cell.Address
                 Exit For
             End If

       Next tyre


    Next cell



End Sub

Private Function getTyresArr()

Dim tyresArr(3)


tyresArr(0) = "71T"
tyresArr(1) = "73T"
tyresArr(2) = "75T"
tyresArr(3) = "104N"

getTyresArr = tyresArr

End Function

Please note this assumes you will only ever have one tyre code per line. you may get some false positives if these strings exist for other reasons. you would need to enter all the codes into the function that returns the array.

Excel VBA Extract Specific Data from Column of Mixed Data

3 Answers3