VBA Excel filter specific string content in a whole column and paste it in another column

Question

I have a matrix of aprox 5000 rows that looks something as seen on im1 (not the real matrix)

Basically I coded something that inserts 3 empty columns to the right of wire/hose/type with the respective names "Wire, Size and Color" written in the top cell of the column

The column Wire/hose/Type contains 3 types of information the first would be the 4 character long string that start with a number (like 3FEC or 4LOP). this is a pattern the second is a number like 2.5 or 0.50 the third is the string that contains one or two colors (GN or GN/BN for example)

What I want to do is Identify the first type of information in every cell of the column and paste it on "Wire", to paste the second type of information on "size" and the third on "color" and finally deleting the column Wire/hose type so finally it looks something as seen on im2:

im2

If anyone could please guide me a little on filling at least the first parameter so i can fill the rest with the same reasoning I would really appreciate it, don't mean to sound cliche but I'm kind of new in this coding environment

my code so far looks like this:

Sheet3.Activate

For i = 1 To 4
    celltxt2 = ActiveSheet.Cells(1, i).Text
    If InStr(1, celltxt2, "Wire/Hose/Tube type") Then
        Columns(i + 1).EntireColumn.Select
        Selection.Insert Shift:=xlToRight
        Selection.Insert Shift:=xlToRight
        Selection.Insert Shift:=xlToRight
    End If
Next i
o = 1
    Do While IsEmpty(Cells(1, o)) = False
        o = o + 1
    Loop
Set wr = Cells(1, o)
wr.Value = "Wire"
Set sz = Cells(1, o + 1)
sz.Value = "Size"
Set clr = Cells(1, o + 2)
clr.Value = "Color"

Thank you!

You need to watch this video:[Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)](https://www.youtube.com/watch?v=c8reU-H1PKQ&index=5&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5&t=3128s) — , Sep 06 '17 at 17:18
It is recommended to avoid the use of select: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — danieltakeshi, Sep 06 '17 at 17:33
What you have tried to do, to break up the information? (e.g. WIRE-3FEC-2.5:WH/VT to 3FEC, 2.5, WH/VT) — , Sep 06 '17 at 17:53

score 2 · Accepted Answer · edited Sep 06 '17 at 18:08

This is the way to do it.

Sub ProcessData()
    Dim x As Long, y As Long
    Dim r As Range
    Dim values As Variant

    With Sheet3
        For y = 1 To 4
            If InStr(1, Cells(1, y).value, "Wire/Hose/Tube type") Then
                .Columns(y).Resize(, 3).Insert Shift:=xlToRight
                Exit For
            End If
        Next
        y = y + 3

        For Each r In .Range(.Cells(2, y), .Cells(.Rows.Count, y).End(xlUp))
            values = Split(Replace(r.value, ":", "-"), "-")
            r.Offset(0, -3).value = values(1)
            r.Offset(0, -2).value = values(2)
            r.Offset(0, -1).value = values(3)
        Next
        .Cells(1, y - 3).Resize(1, 3).value = Array("Wire", "Size", "Color")
        .Columns(y).Delete
    End With

End Sub

VBA Excel filter specific string content in a whole column and paste it in another column

1 Answers1