+ Reply to Thread
Results 1 to 8 of 8

Convert Complex text to Columns in Excel 2010

  1. 08-25-2015, 01:52 PM #1
    Klmo
    Klmo is offline
    Registered User
    Join Date
    08-25-2015
    Location
    Minnesota
    MS-Off Ver
    2010
    Posts
    14

    Convert Complex text to Columns in Excel 2010

    Hello,

    I am trying to divide a complex text to columns in Excel 2010. Each cell looks as the following,

    First Cell
    ---------
    "Cost Center: P00500 Riverwalk Jazz

    Fund: Unrestricted

    Spend Category:Advances Producers"
    "Cost Center: P00685 Tavis Smiley


    Second Cell
    ------------
    "Corporate Credit Card Account: PRRT1008

    Cost Center: P00091 Board

    Employee: Patty Johnson

    Fund: Unrestricted

    Location: PRRT

    Spend Category: PRRT - Prepaids

    Supplier: MP&E AUSTIN LLC"


    So, I'd like to have each line in the cell, in a separate cell. When I tried the Text To Columns Function, it gets funky results? Is there a way to do that?


    Thanks
    Reply With QuoteRegister To Reply
  2. 08-25-2015, 01:56 PM #2
    ranman256
    ranman256 is offline
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,190

    Re: Convert Complex text to Columns in Excel 2010

    what are ALL the possible header names. the most I see are cell2, with 7 listed.
    Reply With QuoteRegister To Reply
  3. 08-25-2015, 02:00 PM #3
    Ron Coderre
    Ron Coderre is offline
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Convert Complex text to Columns in Excel 2010

    Just a thought...

    • Select the single-column range of cells to be parsed
    • Data.Text-to-columns
    ...Check: Delimited......Click: Next
    ...Check: Other...select the character box.....CTRL+j <---That's the keyboard shortcut for a carriage return
    ...Click: Finish

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules
    Reply With QuoteRegister To Reply
  4. 08-25-2015, 02:01 PM #4
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,754

    Re: Convert Complex text to Columns in Excel 2010

    Select the column and do Data > Text to columns, tick Delimited, Next, tick Other, and in the ajacent box, press and hold the Alt key, and on the NUMERIC keypad, type 0010, then press Finish.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  5. 08-25-2015, 02:15 PM #5
    ranman256
    ranman256 is offline
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,190

    Re: Convert Complex text to Columns in Excel 2010

    i may have something to flip them, but I need all the col names, and the data.
    thx
    Reply With QuoteRegister To Reply
  6. 08-25-2015, 02:32 PM #6
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Convert Complex text to Columns in Excel 2010

    Further to Ron Coderre's explanation, here is a picture of what you should see. When you select OTHER and enter Ctrl + J you might not see anything in the box or you might see a dot. The Text all the rest should be as shown.
    Capture.JPG
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  7. 08-25-2015, 03:48 PM #7
    Klmo
    Klmo is offline
    Registered User
    Join Date
    08-25-2015
    Location
    Minnesota
    MS-Off Ver
    2010
    Posts
    14

    Re: Convert Complex text to Columns in Excel 2010

    Thanks everyone for the help.

    Ron Coderre, you rock!
    Reply With QuoteRegister To Reply
  8. 08-26-2015, 06:24 PM #8
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,754

    Re: Convert Complex text to Columns in Excel 2010

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel 2010 Pivot table, add columns so that I can add text comments
    By bethcols in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-13-2014, 07:04 AM
  2. Excel 2010 Convert Formulas to Corresponding Text Based on ID
    By masry6683 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-22-2014, 05:03 PM
  3. VBA code to convert text file to Excel 2010
    By slin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-20-2014, 04:35 PM
  4. Excel 2010-convert text to value
    By Ramsee in forum Excel General
    Replies: 19
    Last Post: 01-15-2014, 07:10 AM
  5. How to Convert Word 2010 doc to Excel 2010 that contains several tables (one macro)
    By theglitch in forum Word Programming / VBA / Macros
    Replies: 1
    Last Post: 01-24-2013, 01:36 PM
  6. Convert Text to Numeric Excel 2010 Pivot Table
    By zcheema in forum Excel General
    Replies: 1
    Last Post: 05-17-2012, 04:58 PM
  7. [SOLVED] convert text labels to excel columns
    By RSF in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-08-2005, 10:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1