Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Split a column into two Columns in excel

  1. #1
    New Member
    Join Date
    Mar 2017
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Split a column into two Columns in excel

    Hello Team,
    I have this below data in one cloumn.
    Cloumn A
    119WATER:FYI0333
    120MATTER:BYT0444
    144CAUSE:PRI0345

    I would like to split this data into the two column
    such as
    Cloumn A
    119WATER
    120MATTER
    144CAUSE

    Column B
    FYI0333
    BYT0444
    PRI0345

    Please advise me on this
    Thanks in Advance
    Cheers
    Waqar

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,352
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    31 Thread(s)

    Default Re: Split a column into two Columns in excel

    Select Column A and then call up the Text To Columns dialog box (Data tab, Data Tools panel)... on first dialog page, select Delimited and then click Next button...on second dialog page, select the Other checkbox and put a colon ( in the field next to it... click the Finish button.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    New Member
    Join Date
    Mar 2017
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split a column into two Columns in excel

    Thanks for your support on this matter. Cheers
    Waqar

  4. #4
    New Member
    Join Date
    Mar 2017
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split a column into two Columns in excel

    Sorry to asked you one more then.

    Could it possible or you to advise me a formula for this steps. Please

  5. #5
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,713
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split a column into two Columns in excel

    In B1 =LEFT(A1,FIND(":",A1)-1) and copy down
    In C1 =RIGHT(A1,LEN(A1)-FIND(":",A1)) and copy down
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.

  6. #6
    New Member
    Join Date
    Mar 2017
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split a column into two Columns in excel

    Great it works
    Heaps of thanks

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,352
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    31 Thread(s)

    Default Re: Split a column into two Columns in excel

    Quote Originally Posted by waqar1239 View Post
    Great it works
    Heaps of thanks
    It does? You specifically said in your first message that you wanted the first part of the text in Column A (replacing what was originally there)... a formula solution cannot do that (short of copy/pasting values over top of the original values and then deleting the duplicate data).
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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