+ Reply to Thread
Results 1 to 7 of 7

Split data into multiple columns

  1. #1
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    Split data into multiple columns

    Hi,

    I want to split data from worksheet 'Data' into the form of data as mentioned in 'Result' worksheet.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,382

    Re: Split data into multiple columns

    Once again you are failing to give fully representative data. What about this type of data that you told us about halfway through your last thread?

    Khitwan Polythene Bag Udhyog (Calami Eng.),Pght(300010459 (Main))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    Re: Split data into multiple columns

    Oops.I forget to incorporate.Thanx for reminding me.

    Kindly take this into consideration assuming in worksheet.
    Last edited by paradise2sr; 09-27-2020 at 10:22 AM.

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Split data into multiple columns

    paradise2sr

    Hello, please explain....

  5. #5
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    Re: Split data into multiple columns

    Plz find in attachment revised and updated including all the cases and complete data.

    To explain again, I have data in one worksheet i.e in "Data".in B2 column and below.From this data I require to separate in 3 columns as mentioned in another expected worksheet i.e namely-"Result".

    The file is also presented in a manner that it is in self explanatory.
    Attached Files Attached Files
    Last edited by paradise2sr; 09-27-2020 at 10:50 PM.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Split data into multiple columns

    In C2

    =IFERROR(LEFT(B2,IFERROR(FIND(",",B2)-1,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9},B2),1)-2)),B2)

    In D2

    =(IF(ISNUMBER(FIND(",",B2)),LEFT(MID(B2,FIND(",",B2)+1,256),FIND("(",MID(B2,FIND(",",B2)+1,256))-1),""))

    In E2

    =IFERROR(MID(B2,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9},B2),1),9),"")

    Copy down all.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2021 / 365
    Posts
    1,238

    Re: Split data into multiple columns

    Thanx a lot.Could you help me in the post >>https://www.excelforum.com/excel-pro...-into-one.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Split columns into multiple rows keeping all other data as is
    By lafm5970 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 11-21-2019, 06:50 PM
  2. [SOLVED] Split multi-line data into multiple columns
    By tax112 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2019, 01:29 AM
  3. Split data into multiple columns and rows based on specific string
    By qiyusi in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-13-2018, 02:01 PM
  4. [SOLVED] Split data into multiple columns
    By rg2014 in forum Excel General
    Replies: 8
    Last Post: 11-05-2014, 10:07 PM
  5. split multiple semicolon separated values into new rows for multiple columns
    By bruno08102013 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 01-05-2014, 05:27 PM
  6. Split uneven data from a single columns to multiple columns
    By pfoz in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-13-2013, 08:24 AM
  7. Split data in a column into multiple columns in excel vba
    By bujji1305 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-21-2012, 09:10 AM

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