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

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

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

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

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

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

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

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

  8. how to insert cell within a cell

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

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

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

  11. Hello,

    Thank you for this article, in which a deep understanding and diligent attitude can be shown.

    I got, however, a problem, that remains unsolved (or I overlooked the solution), though it seems to be quite simple, or at least can be well defined. It goes like this:

    There is a column (let's say column A), containing some technical descriptions, no more then 100 characters long, but some of these descriptions can even be as short as only one word. Just a common characters collection, like alpha, numbers, slashes, dashes, spaces etc. Nothing special there. What I need to do is to split it into 3 columns (say columns B,C,D) of no more then 35 characters each; (content of these columns will then serve another application as subsequent lines of descriptions). Up to now it's pretty simple, but I don't like to have it split in a way that words are cut in half, but rather split it at where spaces can be found. So we can define rules (just for the 1st splitting, for subsequent could be easily transposed):
    1. splitting should occur at space directly before 36th character in column A,
    2. if there is no space between, let's say, 20th and 36th character in column A, splitting should occur after 35th character,
    3. if there's less then 35 characters in column A, then column B = column A, column C is just empty (I mean no things like #ARG!, #VALUE! etc.)

    I would be grateful if you could direct me towards the proper attitude to such a task.

    Thank you again,
    Mario

    1. Hello Mario,

      I think this task is too complex to be handled by formulas. Anyway, very long and complicated formulas will be required, so I'd opt for a macro instead.

  12. I have a question on my data set. I have a bunch of addresses in a data file, but these addresses come from another data source and have been split by an enter (alt-enter in excel). I am looking for a way to split the data based on this enter and have been unsuccessful thusfar. I hope there is any formula to do this, but I fear I'll have to manually insert a ; everywhere to split it.

    What do I mean:
    In Excel if I enlarge my function box (the one in the top where you insert your data) I can see the address perfectly split by enters having a new line for each part of the adress..
    Example:
    11 Mosside Drive
    Tradespark
    Nairn
    IV12 5PN

    unfortunately in the excel field below this is shown as one large string of text..
    Example:
    "11 Mosside DriveTradesparkNairnIV12 5PN"
    Only showing spaces where there are any in a single line.

    Is there any way I could split this based on the enters given? or is my only option to split this one manually??

    Thanks for the help!
    Jeroen

    1. Hello Jeroen,

      No need to split it manually. You can try out his way:

      - Go to Text to columns > Delimited
      - Check the "Other" checkbox, place the mouse pointer in the box next to it and Ctrl+J (it is a keyboard shortcut for a line break).

      You should see the text properly split to columns in the Preview window.

      1. thank you so much this worked for me and i didn't have to write 100 number :-)

  13. Hello,

    I seem to get an error message when using the formulas =MID(A2, SEARCH(",",A2) + 2, SEARCH(",",A2,SEARCH(",",A2)+2) - SEARCH(",",A2) - 2) and also =RIGHT(A2,LEN(A2) - SEARCH(",", A2, SEARCH(",", A2) + 1)).

    It states there are too many arguments

    1. Just to note, I am trying to seperate three words with comma's and no spaces in cell A2. example - Joe,Bloggs,1998

      1. Hi Phil,

        Your second formula is correct. But for some reason it contains long dashes instead of the minus sign, and curly quotes instead of smart quotes. This sometimes happens when copying formulas from the web. Just fix this and the formula =RIGHT(A2,LEN(A2) - SEARCH(",", A2, SEARCH(",", A2) + 1)) will work fine.

        As for the first formula, you have to replace +2 with +1 and -2 with -1, because your data does not contain spaces like in my example. So here's the correct formula for your data:
        =MID(A2, SEARCH(",",A2) + 1, SEARCH(",",A2,SEARCH(",",A2)+1) - SEARCH(",",A2) - 1)

        1. OMG THANK YOU for the " comments.

  14. sir
    i am unable to do the split cells in open office excel sheet how to do it can you give the examples sir.

    1. Hi Chakravarti,

      Sorry I am not able to help with the Open Office, I do not have any experience with it.

  15. This is an excellent article. The best thing are the screenshots that explain all the steps. Thank you very much.

    1. Thank you for your kind words! I appreciate your feedback.

  16. hai shawna

    My name is venkatesh i am working at some office i have problem in excel sheet that is in that excel sheet contain only one cell somany charaters involved in that cell. in the sense a cell contain a,b,c,d, but i need to convert a singlecolumn data in the sense A
    B
    c
    D if you know please help me and give screen shots

    1. Hello Venkatesh,

      I do not exactly understand what you are trying to achieve. If you want to replace commas with line breaks, then select all the cells you want to change, press Ctrl+H, enter comma (or semicolon) in the 'Find What' field, put the cursor in the 'Replace With' field and press Ctrl+J.

      If you need to change the lowercase to uppercase, please check out this article .

      1. I'm guessing, what he's trying to achieve is to separate characters from 1 cell into several columns.

        Eg.
        abcde (1 cell) -> a | b | c | d | e (5 cells/columns)

        My suggestion:
        LEFT(), RIGHT() functions (with relation to LEN() & SEARCH() function).. Google them.

  17. Hi Your article is great. I'm trying to use this information to split up address but the lenght of the street numbers are names are not uniform. Here is data below. Any idea of what formula i can use to put street number, street name in separate columns?

    Address Street Number Street Name Street Type
    340 Pinemont Rd. Ne
    11414 83 Ave.
    10210 91 St.
    2 Hillman Close
    37 Carswell Rd. S E
    207 10 Ave. SE
    220 3Rd. Ave. W
    4607 45 Ave.
    4425 5 St. E

    1. Use the spaces as delimiters?

    2. Thank you Shawna!

      Regrettably, I cannot suggest any formula for your case. You can check out the Address Assistant add-in that is especially designed for splitting all kinds of addresses.

  18. Thank you for this article. It helped me a lot in my work. Really appreciate this.

    1. Thank you for your comment Karthik! I am really glad to know it was helpful.

  19. We have two excel sheets having salary values with badge id's,both the badge id's contains alpha numeric but numeric. We have to pull the values from sheet #2 to Sheet #1 by using formula,please advise

    Example :
    Sheet#1 : ASDF123 (Badge ID)
    Sheet#2 : FDSA123 (Badge ID)

    1. Hello Ashok,

      You can do it in this way:

      - On Sheet#2, create a helper column "Badge ID Helper" and copy this formula across the "Badge ID Helper" column:
      =MID(A2,4,1)&MID(A2,3,1)&MID(A2,2,1)&MID(A2,1,1)&MID(A2,5,4)

      - Merge Sheet #2 and Sheet #1, by matching "Badge ID Helper" and "Badge ID" on sheet#1. You can use our tool, Merge Tables Wizard for easy merging. If you prefer to use Excel formulas, please check out this article - Look up with Lookups in Excel.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)