Text to column while concatenating select values

Question

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

It depends on the size of your data. If you have like a thousand of lines, I'd still say that your method is the best. If it's not that big, maybe less than 100 lines, you can try looping through the Text file per line without loading it to Excel and just get the bits of info you need. You can start [HERE](http://stackoverflow.com/questions/11528694/read-parse-text-file-line-by-line-in-vba) if you want to experiment on that route. I did a similar post [HeRe](http://stackoverflow.com/questions/23354764/how-to-execute-open-and-close-function-on-multiple-text-files). — L42, May 07 '14 at 01:25
The source also isn't from a text file. It's from within the same excel sheet. — ryano, May 07 '14 at 01:49

Tim Williams · Accepted Answer · 2014-05-07T16:28:56.657

1

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

edited May 07 '14 at 16:28

answered May 07 '14 at 06:30

Tim Williams

154,628
8
97
125

The logic for the code looks good but for some reason I'm getting a "Compile Error" using that code when put in it's own `Sub Sorting()` and `End Sub` context. – ryano May 07 '14 at 13:21
Fixed - missing some `&` – Tim Williams May 07 '14 at 14:44
Yep that did it. I also added an `On Error Resume Next` to the beginning to accommodate for blanks. Thanks – ryano May 07 '14 at 16:21

Text to column while concatenating select values

1 Answers1