Comments on: How to split cells in Excel: Text to Columns, Flash Fill and formulas

In this article you will find several options to split cells and entire columns in Excel 2016, 2013, and lower. See how to use Text to Columns to split cell contents by delimiter, Flash Fill to separate cells automatically, and formulas to extract names, text and numbrs in different cells. Examples and screenshots will help you choose the right approach for splitting your data in Excel. Continue reading →

Comments page 2. Total comments: 189

  1. Hi,
    What is the simplest method for the

    e_mails of one account entered in excel automatically
    and also the content of the e_mail spllited automatically into different cells

    without a human present ?

    Thanks in advance

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  2. Thank you very much, you saved me..

  3. Hi,

    Please,
    I would like to separate these kind of cells: Nödinge Stommen 1:62 into
    Collumn one: Nödinge Stommen
    Collumn two 1
    Collumn three 62

    Thank you so much in advance

  4. Hi, please i have an inventory report on excel, i want to know how i can sort out or separate those having quantity as 0 from others having number1....

  5. Hello,
    I am impressed with your article. In my data set, I have 15 order numbers and names separated by ";".
    Example (Dummy data with only 4 order numbers ans names) in cell A1
    12345;Tom
    12347;Jerry
    123458;Marvel
    1234590;Avengers
    How do I split the above cell in different columns and rows?
    Let me know if i need to provide other details.
    Thank you for your help:)

    1. Hello!
      To split your text line by line, copy it (Ctrl + C), then paste (Ctrl + V) into MS Word, and then from Word copy it back to the Excel sheet. Then, to split the text into columns, use any of the methods described earlier in this tutorial.

      1. Thank you, it worked :)

  6. Dear Team,
    Please sort out the issues in excel

    I have the data for
    M10
    M3
    M35
    M45
    1/8
    5/16
    0.562
    M1.1/4
    I want to sort out the data

    Please clear the issues

  7. Thanks for your guidance.
    My query is,
    (6) AHMEDABAD-H.O. ABC BANK PRAHLADNAGAR 5771234560306
    (6) AHMEDABAD - H.O. ABC BANK 557912314585
    (6) AHMEDABAD - HO XYZ BANK LTD 00678787800073
    (6) AHMEDABAD-H.O. CBI 371010101070
    (6) AHMEDABAD-H.O. PQR BANK 015784512300
    (6) AHMEDABAD-H.O. ABC 50987654321002

    SO how to separate only numbers in above date as data is not in same structure?

  8. Siva564794
    5637489sudi
    Sankar5749Siva
    Can you spilt this data

  9. HAI,
    I HAVE A DATE IN THE FORMAT 13-12-2019. NOW I WANT THIS DATE TO BE FIT IN 10 EXCEL COLUMNS.
    CAN SOMEBODY HELP ME IN GIVING THE FORMULA FOR THIS.

  10. If we have one cell that can say something like "24 hours, 72 hours, 1 week, 2 weeks, 4 weeks, 8 weeks, 12 weeks" (there can be many different possibilities in this cell), is there a way to display results in multiple cells of that same row, but instead of just displaying the words "24 hours", "1 week", etc, we need it to instead display an actual date in those multiple cells, each cell calculating the actual date based on another cell ("Start Date" field which is a date field), so one cell would display the date that is 24 hours after the Start Date, the next cell would display the date that is 72 hours after the Start Date, the next cell would display the date that is 1 week after the Start Date, and so forth and so on. I'm thinking this can either be calculated out before or after doing the text-to-columns thing? Ideally calculated out DURING the text-to-columns thing so it is done in one fell swoop. Does this make sense?

  11. How do split up the below value using excel formula.
    1. 14"x6" = for this I need the output as like: 14" 6"
    2. 12.50 x 2.50 x 0.50 Inches = for this I need the output as like: 12.50" 2.50" 0.50"

  12. Dear all,
    Can you please help me to split some numbers from the below listed items?
    112.74|HOURS|||4126949_Draftsperson / Senior CAD Operator - 413841 - NT
    158.32|HOURS|||4400936_Principal Engineer - 417430 - NT
    173.2|HOURS|||3968060_Senior Principal Engineer - 376731 - NT
    I want to get 4126949,4400936 and 3968060.
    Thanks,
    Dan.

  13. Dear all,
    I have a document with a lot of cells with each cells several lines of data in it. I am looking of a way to separate the different lines into different cells. How can I do that?

    Buffet breakfast included.
    Free unlimited access to Wild Wadi waterpark™.
    Resort credit from AED 200 to AED 2000 as per booked room category per stay, to be used on selected dining and spa options.
    Free parking.

  14. Name & No. separation formula.

  15. Dear Sir/Mam,

  16. How should I convert the data below (break the text at the mentioned points?
    Q1. The difference between the local government in India before and after the Constitutional Amendments in 1992:
    1. It has become mandatory to hold regular elections to the local government bodies.
    2. 1/3rd positions are reserved for women.
    3. Elected officials exercise supreme power in the government.
    Select the correct answer using the code given below: (break)
    a. 1 only (Break)
    b. 1 and 2 only (Break)
    c. 1, 2 and 3 (Break)
    d. 2 and 3 only (Break)

    Thanks in advance

  17. Please help to use only text manipulation (i.e., MID, CONCATENATE, etc.) and/or logical and conditional formulas (i.e., IF, AND, etc.) to populate cells for:
    Product Sales COGS Color Sales COGS Size Sales COGS
    Text details as:
    Product1.5000.500.Orange.5.2.XS.2.1
    Product2.7500.1000.Blue.4.2.S.4.2
    Product3.2500.200.Red.3.1.M.10.4
    Product4.8000.2000.Pink.3.1.L.20.9
    Product5.10000.1750.Grey.5.3.XL.30.16
    Product6.1500.75.Green.3.1
    Product7.4000.600.Yellow.7.3
    Product8.8500.1750.Purple.9.4
    Product9.11000.3500.Brown.8.4
    Product10.9000.4000.White.6.3
    Black.11.5
    Tan.7.3
    Teal.6.3
    Maroon.10.4
    Olive.3.2

  18. Hi,Ekaterina.
    I have did formulas length more than 15,000, couldn't save excel getting error "formula is too long. Formulas may not exceed 8192 character" any chances to overcome this
    +973-33082946

  19. What if i have:
    100.00% Owned AS,100.00% Owned GU,100.00% Owned MP,100.00% Owned PR,100.00% Owned UM,100.00% Owned US,100.00% Owned VI,100.00% Owned BR,100.00% Owned BR,100.00% Owned AS,100.00% Owned BR,100.00% Owned GU,100.00% Owned MP,100.00% Owned PR,100.00% Owned UM,100.00% Owned US,100.00% Owned VI
    And i want it to separate to columns: 100(1st column) AS GU MP PR UM US VI(2nd column) 100(3rd) BR(4th) 100(5th) BR(6th)

    Can u help solve this?

  20. How can do in one excel cell given below

    "
    Reasons for decrease:

    Reasons for Increase

    Variation Explained - "

  21. need a formula to split the address in 4 cells that should not exceed more than 25 characters and minimum should be 3 characters
    For Ex:
    45/232A Nehru street Block 5G Kamaraj Building Hyderadad

  22. Hi svetlana madam,

    i need your help for solve this problem

    stock on godown computer "55nos" printer "200nos"

    some branches need computer and printer example

    branch1 need computer "50nos"

    branch5 need computer "25nos"

    branch8 need computer "5nos"

    we want show our stock details in next cell, i'm used vlookup formula but here showing full stock details "55nos"

    i need branch1 show "55" stock, branch5 show "(55-50=5)" and brnach8 show "zero" stock

    please help me ....as early as possible

    regards
    midhi

  23. Hi svetlana madam,

    i need your help for solve this problem

    Stock require and Allocation details

    branch name item required qty Sock allocation(need this column calculation formula)
    branch 1 computer 50 55
    branch 2 mouse 10 0
    branch 3 printer 15 200
    branch 4 lap top 20 0
    branch 5 computer 25 =55-50
    branch 6 keyboard 30 0
    branch 7 mobile 35 0
    branch 8 tablet 40 0
    branch 9 mobile cover 45 0
    branch 10 tablet cover 50 0
    branch 11 printer 55 =200-15

    Stock on godown
    item qty
    computer 55
    printer 200

    please help As early as possible.....

    with regards
    midhi

  24. hi,

    can you please help me to split my data as these are in this format.
    Base Data
    aaa-gg1tttttttttttttt/01 Term Insurance Acc/45125 12 july 1999
    aaa-gg1kkkkkkkkkk/02 Family Insurance Acc/45126 12 july 1998
    aaa-gg1tttttttttttttt/03 Flotter Familly Insurance Acc/45129 12 july 1996
    aaa-gg1kkkkkkkkkk/03 Fixed Insurance Acc/45130 12 july 1992

    and split required like this.
    column1 aaa-gg1tttttttttttttt/01 column 2 Term Insurance Column3 Acc/45125 Column4 12 july 1999

  25. Thank You for this information, i am enjoying flash function for my validation purpose and i am enjoying learning new things.

    Thanks again for all the hard work you have done

  26. Hi,
    Is it possible to separate January-Mar to values January, February, March?

    Thanks!

  27. how to spit in one cell name is A

  28. Hi,guys
    can you help me please how to separate a text from the cell
    for example i have a cell 2Aa0;8x2;Fc< and how can i separate the text "Aa" and "Fc" from the cell by a function.

    Thank you

  29. Hi,
    Pls help how to change 20180320 to 20.03.2018 with function concatenate combine with right,mid & left .
    Thanks

    1. =RIGHT(A1;2)&"."&MID(A1;5;2)&"."&LEFT(A1;4)

  30. I have a names list of 600 people. Somehow text to column does not work to seperate them, actually it only works for people with 3 names for some reason. please help me. thanks.

    1. Hello,

      I'd recommend you to have a look at our Split Names tool that can help you to detach different parts of the names and place them in separate columns. The add-in comes as a part of our Ultimate Suite for Excel. You can download and install the fully functional 14-day trial version of Ultimate Suite using this direct link. After installation, you'll find the Split Names tool in the Transform section under the Ablebits Tools tab.

      If you don't get the result you need, then you can send us a small sample workbook with your source data and the result you want to achieve. We'll look into your task and try to find a better solution.
      Please shorten your table to 10-20 rows / columns and email it to support@ablebits.com. Please also don't forget to include the link to your comment in the email.

      Thank you.

  31. its very helpfull for us lots of time reduced, thank you very much

  32. Is there a way to split up a blank cell more than twice, giving one more than 4 boxes in 1? I need to have around 6 blank boxes in one cell, almost like a mini-table to indicate 6 different numbers in one cell. I have used the format cells function and so far the most I can get is two divisions in one box but I need 3 or 4.

    Thank you for your time.

  33. Dear Friends

    Please help in Excel Sheet

    Cell 1 Cell 2 Answer
    123 456 123456

    Kindly provide Formula

    1. Hello,

      If I understand your task correctly, please try to enter the following formula in cell C1:

      =A1&B1

      where cell A1 is “123”, cell B1 is “456”

      Hope it will help you.

  34. Hi Ablebits.com Team,

    first thank you .

    I have different no in column a b c d f with blank cell, wan't arrange all no in one column accept blank cell . Please suggest

  35. Hi,

    How do i split the following cells into separate cells using a formula.
    I have managed to get the far left and right values but I'm struggling to extract the two center values. I'm at a loss with this. Will highly appreciate any help. I know i can use text to columns but this isn't ideal.

    1:1-1-1
    10:2-3-4

    1. Hi, James,

      to separate the second value on the left, try this:
      =LEFT(RIGHT(A1,LEN(A1)-FIND(":",A1)),FIND("-",RIGHT(A1,LEN(A1)-FIND(":",A1)))-1)

      to separate the third value, use the following:
      =LEFT(RIGHT(A1,LEN(A1)-FIND("-",A1)),FIND("-",RIGHT(A1,LEN(A1)-FIND("-",A1)))-1)

      You may find these articles helpful:
      LEFT function
      RIGHT function
      FIND function

      1. Hi,
        Could you help me to seperate 4, 5 and last value. Thanks in advance

  36. Good day

    How do I split column that has 2 different data, into 2.
    Here is an example :

    RED APPLES 10 KG SOLD
    GREED APPLES 12 KG AVAILABLE
    GREED APPLES 5 KG AVAILABLE
    Green apples 5KG AVAILABLE
    Green apples 7KG SOLD
    Red apples 3KG SOLD
    Green apples 2KG SOLD
    Red apples 9KG AVAILABLE
    Red apples 15KG SOLD

    I want to create excel spreadsheet with 2 separate coulombs for AVAILABLE and SOLD.

    THANKS

    1. Good day to you too,

      well, if you want to use the formulas, let's suppose that your data is in Sheet2, column A.

      You could try this formula in Sheet1 column A (A1, and then copy the formula down):
      =IF((ISNUMBER(SEARCH("sold",Sheet2!A1)))=TRUE,Sheet2!A1,"")

      and this in Sheet1 column B:
      =IF((ISNUMBER(SEARCH("available",Sheet2!A1)))=TRUE,Sheet2!A1,"")

      Then you will only need to delete empty cells.
      Hope this helps!

  37. how to creat formila column to text in excel

  38. __xxx
    ___xxx
    ____xxx
    _xxx

    XXXX
    XXXX
    XXXX

  39. hello there,
    I have a text which I paste on excel but the problem is the text contains space in the beginning for example:
    xxxx
    xxxxx
    xxxxxx

    I want it to be like this
    xxxx
    xxxx
    xxxx

    I know text to column with fixed width will fix this but I need a formula to arrange this file to start from the beginning

    best regards,
    ali

  40. How do I split text paragraph content into cells?
    I have the following text (questions and answers) in one cell and would like each question and answer to become it's own cell in a column. Is this possible? Can I for example get the "Project Goal" and its answer into a different cell either to the right or below?

    • PROJECT GOAL?:

    Understand unique factors in shopping for "high consideration" items, across categories.

    Could also hit bundling and DEX / delivery notification issues.

    • WHAT QUESTION(S) ARE YOU TRYING TO ANSWER WITH THIS RESEARCH?:

    How do customers shop for high-consideration items?

    • WHAT IS YOUR TIME FRAME FOR NEEDED RESEARCH?
    [Please include any milestones, launch dates, dependencies.]

    None. Discovery oriented project.

    • PROJECT PRIORITY - LOW, MEDIUM, HIGH?:
    [If high, why?]

    Medium.

    Thanks!!

  41. In One Cell I have putted the 3 dates Together (Eg 03-12-16/02-01-2017/2-12-16) and in another sheet I only want the Date which is the latest What I have to do please Help me out

  42. Dear,

    when I select product name in Colum-A(Product name)and auto data input in Colum-B (Product Rate), Colum-C (Product Part No),

  43. Dear,

    i have a question for excle...
    how can divide a single call into two parts.

    please help

    Thanks

    1. Hi Dear use taxt to column formulla

  44. In excel one is A B C D E another is 1 2 3 4 5, now i request u how to seperate lines 1 to 5 are mixed.

    pl. explain in excel sheet.

  45. How to extract number from text
    AP1P2L3E

  46. Rupesh(1-34)+SP(35-36)+Rupesh(37-41)+SP(42-48)+Rupesh(49-59)+SP(60)+Rupesh(61-90)+SP(91-96)+Rupesh(97-103)+SP(104-108)+Rupesh(109-115)+SP(116-120)+XD(121-144)

    How to take
    In a Column 1-34,37-41,49-59,61-90,97-103,109-115
    In next column 35-36,42-48,60,91-96,104-108,116-120
    and in next column 121-144

    Help me if you have any suggestion.

  47. how to split only number into that text(Hoshangabad121904Hasalpur)

  48. Superb article - managed to use the formulae so don't have to convert text to columns from a pivot every week!

  49. As i am exporting data from customised software to excel the work orders looks like
    30\EE-III\2016-17 1st part bill and
    30\EE-III\2016-17 2nd part bill
    now i want to remove 1st part bill using formula. at present i am using cursor to remove. please help ASAP

  50. i want to split a single cell in to two column
    how can possible

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)