Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

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

  1. #1
    Join Date
    25th June 2007
    Posts
    88

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by vpschroeder; May 24th, 2009 at 13:07. Reason: forgot attachment

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd April 2007
    Posts
    3,335

    Re: Convert Column Of Strings To Parsed Matrix

    This should do what you want.
    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697

    Re: Convert Column Of Strings To Parsed Matrix

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

  4. #4
    Join Date
    25th June 2007
    Posts
    88

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    23rd April 2007
    Posts
    3,335

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697

    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Split Single Column Into Multiple Columns And Rows
    By Steve_JR in forum EXCEL HELP
    Replies: 5
    Last Post: February 1st, 2011, 03:03
  2. Replies: 11
    Last Post: May 13th, 2009, 15:12
  3. Split Comma Delimited Single Column To Multiple Columns
    By sunny_90210 in forum EXCEL HELP
    Replies: 1
    Last Post: February 29th, 2008, 07:48
  4. Split Single Column Into Multiple Columns
    By dclark0699 in forum EXCEL HELP
    Replies: 2
    Last Post: July 19th, 2007, 23:40
  5. Split Cells Into 2. Space as Delimiter
    By Koski in forum EXCEL HELP
    Replies: 8
    Last Post: October 18th, 2006, 18:56

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno