Split Words In Single Column To Multiple Columns Using Space As Delimiter

[vpschroeder]

I have utilized Dave Hawley's helpful thread:

http://www.ozgrid.com/Excel/extract-words.htm

to parse individual words out of a column of sentence data (see attachment)

Im running into calculation issues as the initial column data that I actually use is usually 1000's of rows long, and what i am running into as a result is a 'very long' wait for Excel to calculate the gigantic resulting matrix (shown on the right) after i paste in a new data set.

1)Would a VBA code that did the same thing be faster, and 2) help would be appreciated on what that code would look like!

Another added benefit of the VBA is that i could control when the actual calculation of the resulting matrix would occur.

I have annotated the spreadsheet with some important parameters.

Thanks in advance!

[mikerickson]

This should do what you want.

Sub test() 
    Dim headerRow As Range 
     
    With ThisWorkbook.Sheets("sheet1").Range("E:E"): Rem adjust 
        Set headerRow = .Rows(2): Rem adjust 
         
        With Range(.Cells(headerRow.Row + 1, 1), .Cells(.Rows.Count, 1).End(xlUp)) 
            With .Offset(0, 2) 
                .FormulaR1C1 = "=TRIM(CLEAN(RC[-2]))" 
                .Value = .Value 
                .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, _ 
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ 
                Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(1, 1) 
                 
                With Application.Intersect(headerRow.EntireRow, .CurrentRegion.EntireColumn) 
                    .FormulaArray = "=COLUMN(1:1)" 
                    .Value = .Value 
                End With 
                 
            End With 
        End With 
    End With 
End Sub 

[Dave Hawley]

Why not just Text to columns... with a space as your delimiter?

[vpschroeder]

good question - early in my design phase, i probably ruled text to columns out for a few reasons.

however as of right now, i believe text-to-columns may now work. im not quite sure if text-to-columns also trims the data at the same time..if not, i think with some other code this forum has helped me out with recently, i may be able to figure out how to apply a trim to the whole resulting matrix..

in an nutshell - thanks for the code, and the appropriate reminder of the KISS principle

[mikerickson]

The posted code uses the "treat consecutive delimiters as one" feature of Text To Columns.
This is similar to TRIM except that it doesn't remove leading spaces.

[Dave Hawley]

--------------------------------------------------------------------------------

good question - early in my design phase, i probably ruled text to columns out for a few reasons.

IMO, we should never rule anything out. Every feature/idea is a good one for 15mins