SubscribeSplitting text in excel using formulas
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
|
Trackbacks & Pingbacks
- Pingback by Handling spelling mistakes using excel - Custom Formual for performing FUZZY text searches | Pointy Haired Dilbert - Chandoo.org on September 25, 2008 @ 2:35 am
- Pingback by Excel Formulas - Wishlist | Pointy Haired Dilbert - Chandoo.org on January 13, 2009 @ 7:52 am
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
Adjust width of Column Shortcut Alt,...
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?
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.
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.
Hi its a great help although
@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.
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