Splitting text in excel using formulas

Posted on September 8th, 2008 in ideas , Learn Excel - 37 comments

Often when you are processing text using excel it is important to split the text in to multiple parts based on a delimiter. For eg. you may want to divide this|needs|to|be|split in to five parts this needs to be split. There is a simple way to do this in excel, using “import text” option. But this is not always preferable, especially if you need to split text as part of a large process, then you may want to do this using formulas.

Here is a simple approach that you can use to split text using find() and mid() spreadsheet formulas.

split-text-using-excel-formulas

Click here to see the example on Google Docs Spreadsheet.

Assuming the text you want to split is in Cell B2 and the delimiter is space ” “,

  • First we need to find which locations in the text has space in them. Using find() we can do this. So, in cell c2 we will write our first find() as find(" ",$b$2) and in cell d2 we will write find(" ",$b$2,b3+1). Now we can copy cell D2 and paste in cells E2, F2 … n2.
  • Now in Row 3, we will get the split text using a simple MID() spreadsheet formula. In cell B3, the formula would look like, =mid($B$2,1,C$2) and from C3 onwards we can use a formula like =mid($B$2,C2,if(iserror(D2),99,D2-C2)). Essentially what we are doing is, for the first split of the text we are using 1 as starting position and C$2 (location of first space in the text) as the ending position. For the subsequent splits we are using previous space as starting position.
  • That is all. The good thing with this technique is that you just need to write 2 different formulas and then you can use copy paste to get all the text splits. Of course you may want to use some error handling like I did to avoid #value errors.

More on text processing using excel: Concat() UDF for adding several cells, Initials from names using excel formulas

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

37 Responses to “Splitting text in excel using formulas”

  1. Jon Peltier says:

    If you’re in a UDF state of mind, you could convert the string into an array using VBA’s Split function.

    Dim vSplit As Variant
    vSplit = Split(sLongString, ” “)

  2. Chandoo says:

    @Jon.. thanks for pointing the split function. Of course, the intention was to avoid vba, but in this case it makes total sense to have simple vba instead of a 2 formula method like this. I will post another version of splitting text later with VBA.

  3. Cheryl says:

    I’m confused as to why you wouldn’t just use text to columns using the space as the deliminator?

  4. azmat says:

    Hi Chandoo m really sorry my question is not at all related to excel..but since i can see loads of geeks here so i wanna ask you guyz …..hw can i change the colour of a particular line in TEXTPAD……

  5. Chandoo says:

    @Cheryl : text to columns requires manual steps where as sometimes you may want to automate the splitting process.

    @Azmat: Welcome to PHD and thanks for the comments. I am sorry, but I am not sure I understood your question. Textpad seems like a simplified text editor (not a word processor like WORD or Google Docs). I dont think you can change the color of a line without resorting to some type of formatting changes.

    • Swalih says:

      Hi Chandoo, Very good knowledge.
      But formula in cell D2 should be =FIND(” “,$B$2,C2+1) intead of =FIND(” “,$B$2,B3+1)

  6. [...] SPLIT (text, delimiter, part): that can take a text and split it based on the specified delimiter. We will have to use a formula based alternative to split text [...]

  7. Sajni says:

    What’s the point in including the Google spreadsheet link, the pic is clear enough, google spreadsheet is undownloadable, it is not giving cell references like cell B2 etc.

    • Chandoo says:

      @Sajni.. Welcome and thanks :)

      We often provide downloads or links to google spreadsheets for people who are not able to understand or would like to experiment. I provided the link to gdocs so that anyone visiting it can request for access so that they can see the formula and play with it.

  8. Sajni says:

    Hi its a great help although

  9. Brijesh says:

    Hi Chandoo,

    I am stuck a little in one of my personal projects, could you pls guide me as to how to go abt it. So, here is what I want to do.

    1. Import Google Finance values from web onto Excel http://is.gd/3foXD
    2. Split it by columns
    3. Store the values
    4. Chart it

    Dyanmically if possible. As you can see the URL has parameters that could be used in the query.

    Could you please help, I am at dead end now.

    Regards,
    Brijesh

  10. nice tip … make it very easy to extract information without having to use a macro, and it works in excel as well as other packages (google, openoffice).

  11. Nick says:

    thank you for replying to cheryl’s question as i was wondering the same thing!

    great stuff!

  12. Garuda says:

    DPC Rao garu, awesome work with the website if I havent already told you. I knew that someday I would bump into your website in need of help, like today!

    I am trying to figure out how you can do the above using just 1 row and not 2 rows. I have a column with many such items that I need to parse into separate columns.

    Any help appreciated!

    Cheers,
    GV

  13. Chandoo says:

    @GV :)

    If it is a one-off thing, you can use text import and be done. Else, you would need some sort of helper cells or a really long formula. You can move the helper cells in to some other area of workbook and hide them. See attached.

    http://img.chandoo.org/playground/text-split-gv.xlsx

  14. Garuda says:

    Thanks Chandoo, you are right, I ended up using a really long formula. Luckily the file is still stable and doesnt show any signs of weakness yet, so am going ahead with this formula!

  15. [...] you can use formulas to do the splitting, they might become tedious. So the next logical option is to use [...]

  16. Jm says:

    I’m trying to a split a sentence such as “(Nick 09/15/1990 5’8” 220 AVG ERA) … I may have another sentence such as “(Nick Johnson 09/15/1990 5’8′ 220 AVG ERA). I want to split the sentence up by name, birthday, height, weight, and (avg era). I am having trouble splitting names that have both a first and last name accurately as I am using the text to collumns with the space function… it ends up splitting up the first and last name because there is a space in between. This error is also an issue for the avg era function(where there is a space between avg and era)… is there some sort of funtion that I can use to accurately split the string?
    Thanks!
    -JM

    • Hui... says:

      @JM
      Try the following:
      Name: =MID(A2,2,FIND("/",A2)-5)
      Date: =MID(A2,FIND("/",A2)-2,10)
      Height: =MID(A2,FIND("’",A2)-1,5)
      Weight: =MID(A2,FIND("”",A2)+2,3)

  17. JUNAID says:

    I’m trying to a split a sentence sach as “(Nick/1984/220/6)
    Name, birthday, weight & height WHATS I DO?

  18. Sandeep Bali says:

    In first point, it should be C2+1 and not B3+1.

    Thanks for the tutorial :). 

  19. trouble says:

    at last someone that talks in anti-geek

    excellent solution  as does not have limit to amount secondary user can copy and paste …

    ok to my need to enhance the advice ( if that is ok)

    i would like to take a text string delimited by “\”  but not in basic split to columns.

    i would like to take a file location from a saved directory list  and have the filenames  all displayed in same colum ( regardless of number of subfolders precceding the file name) and poissibly  also the file type seperated ( for file type sorting purposes)  into own column ..

    i would like to do this from a simple paste full path into column a  and is split into either location + filename + filetype …    or into dedicated columns  but  entered justified from right rather than left    but also first sorted column containing drive letter and name …

    the reason for wanting no user input  is as i want to use the info  to provide A seperate column for user interaction  with  “new name for file”  option  which using split information will create a new column  (combining other columns plus dos  or windows codes)  to rename files as desired from the easier list few excel provides also allowing copy and pasted formatting options for rename.

    hope this is sort of thing that is ok to ask

    ammeture here  with a little knowledge  making me dangerous

    thanks in advance, and apologies if i have broken a rule

    regards

    ???ò???€?

  20. Redfox3369 says:

    I have a data extract that shows any number of codes stacked in the same cell with a forced line break:

    Pass
    Pass
    TRE05
    WER26

    And there can be anywhere from 2 stacked to 13 stacked.  But i need to get these into individual cells instead of being in an individual cell.  I am looking for a less manual way than inserting rows and cutting and pasting.  Any help would be appreciated.  Thanks in advance.

  21. [...]  to multiple rows.  Notice the delimiter used here is a comma.  We used Excel formulas found here.  In column C we find the position of the comma in the cell, then we use mid() to extract the [...]

  22. balram says:

    i don’t know if i understood correctly but still can’t solve my problem.
     
    I need to split the line below into REf 20 in one column, the title in the middle in another and the country in the final 3rd column. what is the formula i am supposed to use here?

    Ref: 20 | Notebook for new Employees | Germany

    Ref: 22 | Purchase of new Laptop for Research | Kuwait
     
     

  23. Hi,

    i need a solution that i have some data in below given format.

    gunt25256
    vjm6563
    hyd52576
    vsp12546

    so that i need to split the data into two columns one is text column and another one is numeric column

  24. Shilpa says:

    Hi,

    I want to pick user IDs
    data is in format :- Samual, S.
    and i need the output :- samual.sundar

  25. Rohit says:

    Hi Chandoo,

    just i want to know is it possible to separate the values of formulas in two different cell.

    e.g

    suppose , i have insert in coloum A1 +3*7 i get 21 in cell A1 but, i want 3 in cell B1 & 7 in cell C1, is it possible or not??.

    Is there is any formula that can solve this??.

    Rohit

  26. Chandra says:

    Hi Chandoo,

    This is really helpful, however, there is an error in your formula in the following section-

    “First we need to find which locations in the text has space in them. Using find() we can do this. So, in cell c2 we will write our first find() as find(” “,$b$2) and in cell d2 we will write find(” “,$b$2,b3+1). Now we can copy cell D2 and paste in cells E2, F2 … n2″

    In D2 the formula should be =FIND(” “,$b$2,C2+1)

  27. ApocMZ says:

    if I have something like

    MondayTuesday
    note that some cells will have five days in a string
    and I want to split the following into seperate columns, what else can i do, beside =Find(“y”,E2). it will the number of characters, but i want to extract the word.

    • ApocMZ says:

      correction it will give me the number of characters, but it the column with the data is a few thousand rows. I tried the following

      =LEFT(E2,FIND(“y”,E2)) which worked for the first column. But i couldnt something to work properly in any of the following columns.

      Thanks

    • Hui... says:

      @ApocMZ
      Assuming your data is in A2:A100
      B2: =LEFT(A2,FIND(“day”,A2)+2)
      C2: =IF(LEN($A2)-LEN(SUBSTITUTE($A2,”y”,””))>=COLUMN()-COLUMN($A2),MID($A2,FIND(“y”,$A2,SUM(LEN($B2:B2)))+1,FIND(“y”,$A2,SUM(LEN($B2:B2))+1)-SUM(LEN($B2:B2))),””) Ctrl+Shift+Enter
      Copy C2 to D2:H2
      Copy B2:H2 down

      If you copy this formula you may have to retype the ” signs as they are the wrong characters

      Have a look here: http://chandoo.org/wp/wp-content/uploads/2014/07/ApocMZ.xlsx

  28. Hi Chandoo,

    Mr. Dhananjay Upadhyay

    I just want middle name as “Dhananjay” by using formula “”=MID(D2,FIND(” “,D2,1),FIND(” “,D2,FIND(” “,D2,1)+1)-FIND(” “,D2,1))”
    Can you please make me understand the step by step function.

    • Hui... says:

      @Surendra
      I would use
      =MID(A1,FIND(” “,A1)+1,FIND(” “,A1,FIND(” “,A1)+1)-FIND(” “,A1))

      Mid(A1, Start, Length)
      A1 is the text you want to search

      Start is the location of the first character you want to start at
      in this case it is the first character after the first space
      =FIND(” “,A1)+1

      Length is the number of characters between the first and second space
      The location of the first space is FIND(” “,A1)+1
      The location of the second space is FIND(” “,A1,FIND(” “,A1)+1)

Leave a Reply