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

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

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

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

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

  5. How to extract number from text
    AP1P2L3E

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

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

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

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

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

  11. I want separate text from number in Microsoft 2007

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

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

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

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

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

  17. Hi,

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

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

    Joe

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

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

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

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

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

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

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

  26. Thank YOU !!

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

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

  29. how to split or deviation one cell

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

  31. Hi Svetlana Cheusheva,

    You are simply Awesome in Your job! The way you defined things in order excellent! Thanks

  32. Are you saying that Excel cells cannot be split the same way they can be split in Word?

    1. Hello, Mary Jane,

      Could you clarify what you mean by splitting cells in Word?

  33. Hi Siva

    you can use formula =CONCATENATE(A1,B1)
    Like A1 is aaaa and B1 has 111111 so the result should be aaaaa 11111
    Thanks

  34. You are great and doing good job, Svetlana Cheusheva

  35. This is my sheet.
    aaaaa
    111111
    bbbbbb
    2222222
    cccccccc
    33333333
    ddddddd
    44444444

    And i need to achieve like this.
    aaaaa 111111
    bbbbbb 2222222
    cccccccc 33333333
    ddddddd 44444444

  36. HI,

    I want to know the formula how to separate from the below format

    "{otherPartySiteInstanceId:[94462982],
    duLocatorId:[Dom Ult has been subscribed already with Locator 1001034331.],
    otherPartySiteId:[86485522],
    businessRelationshipItemId:[3584087],
    guLocatorId:[Global Ult has been subscribed already with Locator 1001034331.],
    organizationID:[19989256],
    businessRelationshipId:[502011447],
    customerLocatorID:[1003343591],
    addressCountryLanguageId:[126951852],
    addressId:[25175801]}"

    I need to separate the organization id to next column, format might be different
    Any ideas on how I can do this?

    thanks in advance

  37. Hi
    Pls help me that in excel can it possible that i want fill a column figure and the next two or three column auto fill with special text (for example if i fill in column A value 100 and column B & C auto filled "PAID"

  38. This is great stuff, but I have one that I can't figure out and would love some input. I have a column that includes numbers and dates. I need to move the date rows to a new column:

    0.5h
    0.5h
    0.25h
    TUE, MAR 24, 2015
    1h
    1h
    WED, MAR 18, 2015
    0.5h
    0.5h
    TUE, MAR 17, 2015
    3h
    FRI, MAR 13, 2015
    0.75h
    THU, MAR 12, 2015
    0.5h
    4.25h
    WED, MAR 11, 2015

    Any ideas on how I can do this? I can't event select the date fields and do a copy and paste because it doesn't leave them in the same row...

    1. Hello,

      Since it's necessary to check the data formats, you need a special VBA macro for your task. Sorry, we cannot help you with this.

  39. Hai i am harish in want two answer first how can i divided in this number 1(23)345 like 1 23 345 in three column kindly in use right left and mid formulas

    secend how can i convert in number to word in excel

  40. thanks for your information, i have one dought one sentence in
    one column that is "branch, sno, subject, name, city" in this question i want in A column "sno", B column "branch", and C-"subject", D-"city", E-"branch". Please suggest how it is solved.

  41. I am utilizing google doc, which the responses are downloaded into an excel format of google sheets. In one of the cells, there are about three product names listed. I am needing to detect if there are mutiple words within the cell, and automatically separate them, while copying all of the other information in 5 other cells. The separation needs to be vertical, not horizontal on the spreadsheet. Please advise and thank you in advance.

    1. Hello, Ann,

      Looks like there is no simple solution for your task, most likely you need a special VBA macro.

  42. How to separate text and number.
    exp.
    rakesh400
    mukesh401
    abhi402

    pls

    1. If you always have three numbers to the right, then use these formulas:
      Text: =left(A2,len(A2)-3)
      Numbers: =right(A2, 3)

      Where A is the column with the source data.

      Enter the formulas into two columns and copy them down to the end of the source data.

      If the number of digits after text can be different, then you need a VBA macro to fulfill this task, the formula will be too long.

  43. Hi,
    I would like to know how to split cells horizontally. Is it possible, I know I can do it in a Word document but can't work out if it is possible in Excel.
    I have a spreadsheet with each line for columns A to N with a height of 84.0 (112 pixels). From columns O to W I would like to half the height to 42.0 (56 pixels).

    Is that something that can be done?
    BTW thanks for the above article, I have learnt something I will be able to use.

  44. hi,
    would like to ask if i created a file as text file, i open with excel.
    all the contents in the sheet is in the same column, may i know how can i make it in separate column as what i read in adobe or our system ?
    Ie :
    ACCOUNT : 111 AREA : bbb1

    NO STOCK CODE DESCRIPTIONS QUANTITY U.PRICE DISC AMOUNT

    00000000 CHIC.FLOSS 1KG-ORI(HALAL) 5 37.90 189.50

    its look like in different column, but its not. all the content was in the same column and the content is not in order,difficulty for us to edit the content which it suppose to be in separate column.

  45. Hi Svetlana,

    I have a problem. in one cell i have entered a text, i need to insert a text from another sheet in between.

    data in cell "( City/Centre:__________________)"

    now i want to insert from another sheet a text data or a numeric data which should be as shown below.

    Data in cell after inserting "(Centre / City :_________Bangalore____)"

    The word bangalore should also be underlined.

    Can this be done. if so how to do it. it will be very helpfull in my work.

    Thanks

    Chandra shekar

  46. how to insert cell within a cell

    1. To my best knowledge, this is not possible.

  47. How to remove 91 from phone list . see below details for your references

    Phone#
    91 281 3018308
    91 0281 3018227
    91 0281 3018325
    91 812 87482

  48. Sir,
    Thank you very much for this Fantastic tutorial,.
    Looking forward for your help to resolve the following.
    I have a table as attached below
    3043 ISACN-194 ISA MATHEW PV
    PARASSERIL, KUNNUMKAI, WEST ELERI
    BEEMANADI
    3044 ISACN-195 ISA SREEDHARAN K
    POYYALAM HOUSE, ENNAPARA, KANHANGAD
    ATHIRA TEXTILES, ENNAPARA
    3045 ISACN-196 ISA BABY JOSEPH
    THEKKINIKUNNEL, POODAMKALLU, KASARAGOD
    CHEERS COOL BAR, POODAMKALLU
    3046 ISACN-197 ISA ABBAS
    ORAVANKARA, ERAVANNUR, CALICUT
    HAPPTY BOOK CENTER, BEKAL, KANHANGAD

    in each serial no, the address field is in three rows in single colmn. I have to get this adress column to be split in three seperate columns. means 1,4,7,10 etc in a col and 2,5,8,11 etc in another coloumn and 3,6,9 12 etc in another one.
    pls give a formulae for this.
    thank u

  49. Your article is fantastic - however - I seem to have a bit of a different issue - not sure if it can be done or not
    I have a txt file, opened in excel, and I was able to create 2 columns from 1 so that I have a start to what I want to accomplish:
    COL A COL B
    BEGIN VEVENT
    DTSTART 2010103108000
    DTEND 2010103109000
    DESCRIPTION FIRSTNAME LASTNAME
    END VEVENT
    and the pattern repeats

    Is there anyway I can create separate columns labeled BEGIN DTSTART DTEND DESCRIPTION (from column A)
    and have the info from Column B then fill in the appropriate column?

  50. Hi,

    Having looked through how you have solved so many others problems I hope you can help me!

    I have been importing a table from a website and written quite a bit of excel logic after that import. Problem is the website has now changed and is now no longer an importable table!

    This means the data input from the website now comes into 1 cell of data in one string.

    The data is delimited by an increasing number i.e. 1ChrisCatling2VickyCatling3JamesCatling...
    Can I use the Text to Columns wizard by specifying a number (which is obviously changing) so for example to display
    1 Chris Catling
    2 Vicky Catling
    3 James Catling
    ...

    1. Hello Chris,

      Try to open the website using another browser, it may help. If it doesn't, then you need a small VBA macro, formulas and the Text to Columns feature cannot help you with your task.

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