+ Reply to Thread
Results 1 to 8 of 8

Split data in one single cell to multiple cells with Numbers as delimiters

  1. #1
    Registered User
    Join Date
    04-27-2016
    Location
    Chennai, India
    MS-Off Ver
    2016
    Posts
    6

    Split data in one single cell to multiple cells with Numbers as delimiters

    Hello Team ,

    I need to split data in
    cell A1 "1Apples Red 2Oranges Citrus 4Plums 167Rose Yellow" into column data with numbers as delimiters :

    A B C
    1 Apples Red
    2 Oranges Citrus
    4 Plums
    167 Rose Yellow

    Thanks in Advance.
    Mani

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,888

    Re: Split data in one single cell to multiple cells with Numbers as delimiters

    Use the Flash Fill functionality on the Ribbon

    Look here---->http://forums.excelcentral.com/index...bers-in-excel/
    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

  3. #3
    Registered User
    Join Date
    04-27-2016
    Location
    Chennai, India
    MS-Off Ver
    2016
    Posts
    6

    Re: Split data in one single cell to multiple cells with Numbers as delimiters

    Thanks Alan.

    I have the data in A1 and for flash fill to work shouldn't this be transposed to columns. I don't find a way to split / transpose the data into columns with Numbers as starting string / delimiter.

    i.e

    A1 "1Apples Red 2Oranges Citrus 4Plums 167Rose Yellow"

    =>
    A
    1 Apples Red
    2 Oranges Citrus
    167 Rose Yellow

  4. #4
    Registered User
    Join Date
    04-27-2016
    Location
    Chennai, India
    MS-Off Ver
    2016
    Posts
    6

    Re: Split data in one single cell to multiple cells with Numbers as delimiters

    Excel file with data as is and desired attached.
    Attached Files Attached Files

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,888

    Re: Split data in one single cell to multiple cells with Numbers as delimiters

    This link looks like it may be the solution you are looking for.

    http://www.mrexcel.com/forum/excel-q...o-columns.html

    Tested and worked for your example.
    Last edited by alansidman; 04-28-2016 at 02:40 PM.

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Split data in one single cell to multiple cells with Numbers as delimiters

    This one is pretty crazy.
    Text string is in A2

    Enter this formula in B5 and copy down

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    then enter second formula in A5 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    5 1 Apples Red
    6 2 Oranges Citrus
    7 4 Plums
    8 167 Rose Yellow
    Attached Files Attached Files
    Last edited by AlKey; 04-28-2016 at 04:50 PM. Reason: Added Excel spreadsheet
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  7. #7
    Registered User
    Join Date
    04-27-2016
    Location
    Chennai, India
    MS-Off Ver
    2016
    Posts
    6

    Re: Split data in one single cell to multiple cells with Numbers as delimiters

    Alan Thanks a ton

  8. #8
    Registered User
    Join Date
    04-27-2016
    Location
    Chennai, India
    MS-Off Ver
    2016
    Posts
    6

    Re: Split data in one single cell to multiple cells with Numbers as delimiters

    Alkey Thanks. It is crazy

+ 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. [SOLVED] How to split text & numbers in one cell to multiple cells
    By MaheshK5277 in forum Excel General
    Replies: 11
    Last Post: 02-23-2016, 07:13 AM
  2. [SOLVED] Split data from single cell into multiple records
    By Philangr8 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-05-2015, 07:55 PM
  3. [SOLVED] split informations on single cell to multiple cells
    By ysabelle_etc in forum Excel General
    Replies: 9
    Last Post: 12-02-2014, 03:12 AM
  4. Split text into array using multiple delimiters
    By TKFRMjarvis in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-26-2014, 03:07 PM
  5. Replies: 11
    Last Post: 10-16-2013, 10:21 PM
  6. Excel Split Single Cell Array into Multiple Cells
    By devinpitcher in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 12-07-2012, 07:13 AM
  7. Want to split the data in single cell to multiple column
    By pradeepdeepu_001 in forum Excel General
    Replies: 4
    Last Post: 02-02-2010, 09:11 AM

Tags for this Thread

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