CODEWITHSUNDEEP
vbaexcel
dazzathedrummer
dazzathedrummer

Reputation: 531

Excel VBA - extract parts of string to form a list?

I'm trying to extract some details from some SQL code in order to make a list - specifically: I'm trying to extract nominal codes from a case statement to make a human readable list of nominal codes...I'm wondering if there's a way for VBA to extract the string parts and also output a list?

Here's the code that, for example, we'll say is in cell a1...

when ProfitAndLoss.acno in ('P01200','P01201','P01205','P01206','P01210','P01211','P01220','P01221','P01225','P01226','P01230','P01231','P01235')then 'DirSals'

What I need is...

P01200
P01201
P01205
etc

Upvotes: 0

Views: 327

Answers (1)

David Glickman
David Glickman

Reputation: 790

You want to use the Split function.

    Option Explicit

    Sub makeList()
    Dim parts As Variant
    Dim nextLine As Long
    Dim i As Long

    nextLine = 2
    parts = Split(Cells(1, 1).Value, "'")
    For i = LBound(parts) + 1 To UBound(parts) - 2 Step 2
    Cells(nextLine, 1).Value = parts(i)
    nextLine = nextLine + 1

    Next i

    End Sub

This splits the string up into sections with ' as the delimiter. Then it loops through each part, skipping the first part - when ProfitAndLoss.acno in ('- and the last two parts - ')then' and 'DirSals'. I used step two because each second slice is '-'. Each part is output onto a new line, incremented each time.

Upvotes: 1

Related Questions