+ Reply to Thread
Results 1 to 9 of 9

split string into multiple columns

  1. #1
    Registered User
    Join Date
    09-19-2005
    Posts
    4

    Question split string into multiple columns

    I am attempting to take the following string:
    "companyName - accountName"
    and transpose it to multiple columns so it reads like this
    "companyName" "accountName" in separate columns

    Can some help educate me about the formula I would use to do such a thing?!


    btw: the data came from WORD.

    Thank you,
    bam

  2. #2
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Assuming "companyName - accountName" is in cell A1:

    Cell B1:
    =LEFT(A1,FIND("-",A1,1)-2)

    Cell C1:
    =RIGHT(A1,LEN(A1)-D16-1)

  3. #3
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Go to Data > Text to column
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  4. #4
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Quote Originally Posted by oldchippy
    Go to Data > Text to column
    Yeah, that is easier. Select Delimited, after clicking Next, make sure Space is checked, and use a - in the Other field.

  5. #5
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Easiest, cleanest, quickest probably

  6. #6
    Registered User
    Join Date
    01-21-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: split string into multiple columns

    Quote Originally Posted by jasoncw View Post
    Yeah, that is easier. Select Delimited, after clicking Next, make sure Space is checked, and use a - in the Other field.
    Here you have tutorial which can help in case of some problems: Text to columns

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,178

    Re: split string into multiple columns

    5-year old thread....

  8. #8
    Valued Forum Contributor
    Join Date
    05-07-2014
    Location
    India
    MS-Off Ver
    MS Office 365
    Posts
    313

    Re: split string into multiple columns

    Hello Everyone,

    You can go through this link as well to learn many more things about how to "split a cell into multiple columns".

    http://www.exceltip.com/tips/splitti...ent-cells.html

    Thanks
    Nisha

  9. #9
    Registered User
    Join Date
    06-04-2015
    Location
    India
    MS-Off Ver
    2007,2013
    Posts
    63

    Re: split string into multiple columns

    Hello Everyone,

    You can use below formulas:-

    Company Name:- =LEFT (A2, SEARCH ("-", A2))
    Account Name:- =RIGHT(A2,LEN(A2)-SEARCH("-",A2,SEARCH("-",A2,SEARCH("-",A2)+1)))

    Thanks
    Last edited by Arpita_Excel; 06-05-2015 at 08:45 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1