CODEWITHSUNDEEP
vbaexcel
Adrift
Adrift

Reputation: 59819

Using VBA to filter out a columns content

I have a column that mixes items dimensions with its product name, something along the lines of:

               A
47"H X 18"W Six-Light ChandW Three-Light Pendant
29"H X 38"W X 33"D Eight-Lt Chandelier
40"H X 32"W Four-Light Chand
43"H X 36"W X 29"D Three-Light Sconce
60"H X 50"W Eighteen-Light Chand

... // another 600 rows

I'm trying to import it into our store using Magmi. I want to seperate the product name from its dimensions.

The problem is that some items only have a width/height and no depth while others (most of them) have all three. Using text to columns didn't seem specific enough to filter this data, so I'm assuming I need use VBA.

Being new to VBA, I'm unsure of where to start. Does anyone have ideas/pointers?

Upvotes: 2

Views: 216

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149315

The problem is that some items only have a width/height and no depth while others (most of them) have all three.

Logic:

Find the number of " and it's position. If there is no 3rd " then look for " " after the 2nd " and if there is a 3rd ", then look for a space after that.

Let's say you data looks like this

enter image description here

Code:

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim Delim As String
    Dim Pos1 As Long, Pos2 As Long, Pos3 As Long
    Dim i As Long, lRow As Long

    '~~> This is "
    Delim = Chr(34)

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find Last Row of Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 1 To lRow
            '~~> Get 1st position of "
            Pos1 = InStr(1, .Range("A" & i).Value, Delim)
            '~~> Get 2nd position of "
            Pos2 = InStr(Pos1 + 1, .Range("A" & i).Value, Delim)
            '~~> Get 3rd position of "
            Pos3 = InStr(Pos2 + 1, .Range("A" & i).Value, Delim)

            '~~> Check if 3rd " is present
            If Pos3 = 0 Then
                Pos1 = InStr(Pos2 + 1, .Range("A" & i).Value, " ")
            Else
                Pos1 = InStr(Pos3 + 1, .Range("A" & i).Value, " ")
            End If

            '~~> Extract relevant text into Col B
            .Range("B" & i).Value = Mid(.Range("A" & i).Value, Pos1 + 1)
        Next i
    End With
End Sub

Output:

enter image description here

Upvotes: 1

Related Questions