Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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!
    Attached Files
    Last edited by vpschroeder; May 24th, 2009, 13:07. Reason: forgot attachment

  • #2
    Re: Convert Column Of Strings To Parsed Matrix

    This should do what you want.
    Code:
    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
    Attached Files

    Comment


    • #3
      Re: Convert Column Of Strings To Parsed Matrix

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

      Comment


      • #4
        Re: Split Words In Single Column To Multiple Columns Using Space As Delimiter

        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

        Comment


        • #5
          Re: Split Words In Single Column To Multiple Columns Using Space As Delimiter

          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.

          Comment


          • #6
            Re: Split Words In Single Column To Multiple Columns Using Space As Delimiter

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

            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

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X