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.
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 writefind(" ",$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
43 Responses to “Splitting text in excel using formulas”
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, " ")
@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.
I'm confused as to why you wouldn't just use text to columns using the space as the deliminator?
[...] Split text using excel formulas [...]
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......
@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.
Hi Chandoo, Very good knowledge.
But formula in cell D2 should be =FIND(" ",$B$2,C2+1) intead of =FIND(" ",$B$2,B3+1)
[...] 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 [...]
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.
@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.
But the Google Spreadsheet is read only. You can't see or play with the formulas.
Hi its a great help although
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
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).
thank you for replying to cheryl's question as i was wondering the same thing!
great stuff!
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
@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
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!
[...] you can use formulas to do the splitting, they might become tedious. So the next logical option is to use [...]
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
@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)I’m trying to a split a sentence sach as "(Nick/1984/220/6)
Name, birthday, weight & height WHATS I DO?
In first point, it should be C2+1 and not B3+1.
Thanks for the tutorial :).
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
???ò???€?
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.
@Redfox3369
Select the cells
Goto Data, Text to Columns
Delimited
Tick Other and enter Ctrl J in the Adjacent box
Apply
[...] 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 [...]
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
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
Hi,
I want to pick user IDs
data is in format :- Samual, S.
and i need the output :- samual.sundar
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
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)
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.
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
@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
Worked perfectly thank you!!!
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.
@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)
Hi Chandoo.
Google Sheets has a SPLIT() function where it easily splits text based on a specified delimiter. It only needs the reference cell and the character delimiter as its argument. Here's an animated gif of the formula in action.
https://www.jotform.com/uploads/forum/grade4pagasa/1470821245split.gif
Is there a similar and simple formula in MS Excel 2013?
Thanks.
Hi Chandoo
I have one query, I want to split "One million Three Hundred fifty Thousand and five Hundred seventy five & 30/100 Fils Only" into Two Line (for Cheque Printing)
How can i get thro Formula with out break individual Word
Note First line having limit of 60 char, and rest word should be in second line)
Expecting your reply eagerly
[…] 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 […]
Hi
I am using the TEXTSPLIT function to split apart name, address, city and zip.
The text splits nicely and then I copied it down for all the other addreseses.
However, I cannot see how to sort it. If it is a single row of split data, i can sort it but not rows and columns of data.
So, if below is the "spilled" data, I want to sort by Column D. Is this possible to do as it does not seem as though the data is really in those cells.
Col A Column B Column C Column D
Joe 123 Main Street Chicago IL
Patricia 234 School Street Carmel CA
All help appreciated. Thank you.
You can use SORT() function to sort the spilled data.
Assuming your data is in range A1:D100, use =SORT(A1:D100, 4) to sort the data on 4th column.