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 3. Total comments: 189

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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

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

    Thanks!

  7. how to spit in one cell name is A

  8. 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

  9. 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)

  10. 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.

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

  12. 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.

  13. 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.

  14. 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

  15. 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

  16. 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!

  17. how to creat formila column to text in excel

  18. __xxx
    ___xxx
    ____xxx
    _xxx

    XXXX
    XXXX
    XXXX

  19. 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

  20. 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!!

  21. 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

  22. 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),

  23. 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

  24. 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.

  25. How to extract number from text
    AP1P2L3E

  26. 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.

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

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

  29. 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

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

  31. I want separate text from number in Microsoft 2007

  32. How to split it a column and save into multiple spreadsheet?

  33. Hi, I have a query.

    From one of my reports, I get the name and employee number together in the same field. How can I separate the name and employee number?
    For example, in my report, I get the following:
    Assigned To
    Joylan Andrade G920526
    Rahul Dravid G456789

    But I want to split the cell to show Name separately and Employee Number separately and I want to do it for a list of names in the report.
    It should look like:
    Name Employee Number
    Joylan Andrade G920526
    Rahul Dravid G456789

    Please let me know if you can help :)

  34. I have a problem with the text is too big in a cell. The max. row height is 409 and text length is 3,147 chars in column "C"; let say Cells(19, "C"). How can I split this cells into multiple cells, maybe 3 or 4 cells so I can read the text. For example Cells(19, "C") with text length of 800 chars. Cells(20, "C") with the same length and Cells(21, "C") and so on until I can read the whole text by using
    Rows(index).EntireRow.Insert, etc.
    Please let me know.
    Thanks for your help,
    DU

  35. Dear Svetlana,

    We have data like 19 / 10 / 2011 and we just want only mid character. Please guide how to do it using formula. Please note that the given data are text format and values not dates..

    Awaiting ur reply

    Regards

    1. Hi Pritesh,

      You can use a formula similar to this, where A2 is a text-date:
      =MID(A2, FIND("/",A2)+1, FIND("/", A2, FIND("/",A2)+1) - FIND("/",A2)-1)

      If you are curious to know the logic, you can find the detailed explanation here: How to extract N chars following a specific character.

      Please note, the result will also be a text sting. If you want a number, multiply the above formula by 1.

      1. Would like to explain this formula for separation numeric value from text
        =IF((MID(FORMULATEXT($AH$7),43,10))="TODAY()-B3",IF($AH$7=$AH$10881,RIGHT(C121,10)*2/2,"tick such lamda"),CW121)

  36. Hello Svetlana...

    I have the data with alpha numeric like (RAVI MLKD 300 DD 93 KP 8.9 GG RT NI)
    from that i need the data before first numeric character (That is before 3).
    Ex: i want to split (RAVI MLKD ).

    1. Hi Nani,

      You can use this formula, where A2 is the original alpha numeric string:
      =LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

      For the detailed explanation of the formula syntax and logic, please check out this example: How to split text and numbers in Excel.

  37. Hi,

    I want to know,how it possible are breaking excel cell in 2 part.

  38. hi I need help
    Is there anyway that Excel can split a single Cell in Half Vertically ??

    Joe

  39. Hi,

    need help, I have one coloum with numbers (123456), alpha-numeric (assd34df234). All these are in single coloum, say it "B". I want to separate them into other coloums.
    this is my data
    101928299
    101928300
    0E-XZCF-P14P
    9R-OKKD-FXDT
    9Y-FDRG-J4Z9
    A2-HTZQ-2QWY
    AN-5CAQ-BY9C
    DI-6YWX-52Z3
    DI-A4M7-52P1
    DP-U0N4-8B48
    E6-ORTJ-YCJ2
    101928301
    101928302
    101928303

    I want them in different coloums as per their type.

    thank you.

  40. Good morning or evening.
    I have a 2013 xlsx data sheet that one of the columns contains many characters per row. I need to import this data into a 3rd party software that limits that cell size to 60.
    I need to reduce each row's cell to 60 and then move the the next remaining 60 characters to the row below the first set of 60.
    Basically I am truncating the line and moving data to the next cell below the first cell.
    I am not sure how to do this task. The spreadsheet is 51 k lines and I hope to not to have to touch each line individually.

    Thank you for your consideration

  41. Dear, I have big amount of data regarding clients address. I was provided with 4 lines of address lines from the old system which we want to migrate into new system but he requirement is one address line should not be more than 30 characters.Usually people cramming most words in address line 1 and address line 4 is usually empty. How can I split the address among those 4 lines which each lines should not be more than 30 characters and without changing the meaning of the words?
    Thank you in advance.

  42. I have successfully split my text from numbers but I am now unable to format the numbers column, e.g. change to currency, add the column of numbers etc.

  43. "This amazing 3D light is safe, comforting, cordless and is loved by kids and adults alike!. Looks great on or off. Never gets hot to touch

    $Features & Functions$
    $Material: Plastic/LED bulb$
    $LED bulb included$
    $Design Origin: Canada$
    $License: Nickelodeon$
    $ Product Dimensions:L 48cm x W 25.5cm x H 25.5cm$
    $Product Weight: 1.78 kg"

    Please help me to split the above in to different cells separting at "$"

  44. I am looking to split a cell that contains for example,

    Linear Dimension (3.60 +0.04,-0.04)

    My goal is to split this one cell into 5 different cells containing

    Linear Dimension, 3.60, 0.04, -0.04 & 0.08

    The last being the difference between the upper and lower limits.

    Thank you!

  45. Hi Team,
    I need to split the data and get texts starts with INC and CRQ separately.
    GERP Evolve services - Consumption report June-November 2013 Ann Schroyens € 2,028.22 CRQ700000956750 € 126.74 CRQ700000974595 € 548.04 CRQ700000999790 € 1,005.47 INC700023629023 € 27.88 INC700023661405 € 27.88 INC700023737013 € 139.64 INC700024270751 € 27.91 INC700024290887 € 27.91 INC700024386325 € 27.91 INC700024386855 € 27.91 INC700024506369 € 27.91 INC700024622160 € 13.01 Diego Ferreira € 55.83 INC700024445871 € 27.91 INC700024445922 € 27.91 Evert Vannoppen € 3,075.83 CRQ700000968131 € 3,075.83 Jarod Lee € 40.48 INC700023507897 € 40.48 Pascal Hereng € 186.42 CRQ700000927347 € 186.42 Timmy Torfs € 885.47 CRQ700000855850 € 885.47

  46. Thank YOU !!

  47. How to split 1 cell data to 2 cell data, Ex: A01M01 => A01 / M01

    1. Hi Ratanak,

      For this particular example, you can use the RIGHT and LEFT functions:

      To extract the first 3 chars: =LEFT(A1, 3)

      To extract the last 3 chars: =RIGHT(A1, 3)

  48. Exp.
    NO 86 PANCHALAVAS SONETH PO VAV TE SONETH LANDMARK :
    NO 195 RAJHAPUTWAS PO SUIGAM SUIGAM LANDMARK :
    Dived village & po

  49. how to split or deviation one cell

  50. Hi! What shall I do with my document I want that my first column will see the column j. I find it hard to see the document I need to edit.

    1. Hello, Jedd,

      You can scroll until your column J is the first and click Save. After this when you open the workbook next time, this view will be kept. Or you can hide columns A-I.

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 :)