CODEWITHSUNDEEP
vbaexcel
ryano
ryano

Reputation: 241

Text to column while concatenating select values

I would just like to know if there is a simple way of doing a TextToColumns like command where it gets an array and just joins parts of that array down a column.

Example:

Slected TextToColumns string in cell A2: John Doe 1:00 PM - 3:00 PM
Values to be pasted into cell B2: John Doe
Values to be pasted into cell C2: 1:00 PM
Values to be pasted into cell D2: 3:00 PM

The code I'm using now is a simple TextToColumns sequence:

Sheets("Sheet1").Range("A2:A60").Select
    Selection.TextToColumns Destination:=Sheets("Sheet1").Range("B2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
    Array(7, 1))

After the TextToColumns runs I have 3 CONCATENATE formulas to rejoin the values together. I figure there's probably a better way to handle that information

Upvotes: 1

Views: 92

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

Untested:

dim c, arr

for each c in Range("A2:A60").cells
    If Len(c.value) > 0 then
        arr = split(c.value," ")
        c.offset(0, 1).value = arr(0) & " " & arr(1)
        c.offset(0, 2).value = arr(2) & " " & arr(3)
        c.offset(0, 3).value = arr(5) & " " & arr(6)
    End If
next c

Upvotes: 1

Related Questions