Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Split Text Across Columns

  1. #1
    Join Date
    7th January 2005
    Posts
    490

    Split Text Across Columns

    My problem:
    Need to split the WORD into Col B and put the DEFINITION into Col C.
    Here's an example of what's sitting in A1:
    Title A description of record contents

    I've tried using the text to columns but can't get it to work.
    (since the Words are all different lengths, something gets chopped off)

    What I have today:
    In column A (within a single cell is both the Word & it's Definition).
    I need to extract the word ONLY into a new column (B)
    and extract all the other words into column (C) (without the dash)...

    I've also tried :
    =LEFT(A1, FIND(" ",A1)-1)
    and successfully stripped the Word into column B
    but can't find any functions to extract the rest properly into C

    I JUST noticed, some WORDS are multuple...example:
    Information Protection Level Used to identify information protection values per Pro 2227

    Can you provide a function for doing a 3 word extraction to Col B?
    I guess, what I REALLY need is for it to take "everything up to the dash" and put in column B......then put everything after the dash and put in col C.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Split Words Into Col B, Definition Into Col C

    Hi,

    Try,

    Data > Text to columns.. check Delimited > check Other and type "-" (without quotes) > next > finish.

    HTH

  3. #3
    Join Date
    7th January 2005
    Posts
    490

    Re: Split Words Into Col B, Definition Into Col C

    I already tried exactly that....and it didnt work.
    I went ahead and did it again -- just to confirm.

    I've attached small sample to see for yourself.
    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. 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


  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Split Words Into Col B, Definition Into Col C

    OK. Instead of typing "-", hit Alt+150

    HTH

  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Split Words Into Col B, Definition Into Col C

    Or, just copy the - from the formula bar (Ctrl+C) with any cell in Column active. Then paste it in Other with Ctrl+V

  6. #6
    Join Date
    7th January 2005
    Posts
    490

    Re: Split Text Across Columns

    I'm not sure that I'm following you.

    Sounded like you are saying:
    Peform all the original steps and only change one part:
    1)Click on the column header to highlight Col A
    2)Then, Data > Text to columns.. check Delimited >
    3)and when the 2nd screen appears that allows you check boxes such as OTHER (don't check anything) and hit ALT+150 while you're sitting on that screen, then, > click next > finish.

    When I performed the ALT+150 I was on the screen with checkboxes:
    I tried leaving OTHER UN-CHECKED (but cleared the field where the dash went)
    I tried check-marking OTHER and (clearing the dash field)

    I tried simply doing ALT+150 without entering the text to columns tool and still not seeing anything happen.
    8-(

    Something must be missing.....

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Split Text Across Columns

    OTHER (don't check anything) and hit ALT+150
    check and hit Alt+150

    HTH
    Last edited by Krishnakumar; November 15th, 2006 at 16:38.

  8. #8
    Join Date
    7th January 2005
    Posts
    490

    Re: Split Text Across Columns

    Quote Originally Posted by ChrisOK
    When I performed the ALT+150 I was on the screen with checkboxes:
    I tried check-marking OTHER and (clearing the dash field)
    I've already that, buttried it again and again and there's no response after placing a check in the OTHER box, then, while sitting within the empty field to the right of the OTHER box, I hit ALT+150, ....nothing happens so I proceed by hitting Next and Finish.

    Still, nothing happens?!?
    Have you tried this with the attached file to see if it works?

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Split Text Across Columns

    Hi,

    Try this macro.

    VB:
    Sub TestIt() 
        Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ 
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ 
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ 
        :="", FieldInfo:=Array(Array(1, 1), Array(2, 1)) 
    End Sub 
    
    
    HTH

  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Split Text Across Columns


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Split Text To Columns Across 2 Workbooks
    By sterlingparker in forum Excel General
    Replies: 1
    Last Post: August 18th, 2008, 16:05
  2. Split Text Across Columns
    By luvnlife in forum Excel General
    Replies: 5
    Last Post: June 30th, 2008, 05:01
  3. Split Text Across Columns With Delimiters
    By bob135 in forum Excel General
    Replies: 2
    Last Post: April 17th, 2008, 18:54
  4. Split Text Across Columns
    By cbueno in forum Excel General
    Replies: 3
    Last Post: December 9th, 2006, 13:26

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