Text Split with Excel Formula – Part I
July 13, 2008
It always happens that you need to use text to column feature in excel. for some it’s rare but from some it’s everyday exercise. Think about the advantages that you will get if you can split text with formulas.
Let’s see how you can do that with example:
If you have a string ‘Firstname Lastname’, in A3 how you will separate this text with formulas so that you will have firstname and lastname in separate columns.
There are two formulas involved.
Left and right
How to Use left formula for fisrtname:
the main factor in this formula is finding out the string length of the firstname as it will vary. The solution is find out the place of separator (space in given example)
The formula for given example will be… =left(A3,find(” “,A3,1))
Now the difficult part of it that is the right formula use for lastname:
here as well the main factor is finding out the length of the string lastname as it will also vary everytime. so to find out the length we will first find out the place of separator (space in given example) and reduce it from the total length.
The formula for given example will be… =right(A3,len(A3)-find(” “,A3,1))
In next part we will see how can we use the formulas to split text “Firstname Middlename Lastname”.
Entry Filed under: Excel, Formula, MS EXCEL, MSEXCEL. Tags: Excel, Formula, MS EXCEL, MSEXCEL, text, text formula, text separate, text split.
4 Comments Add your own
Leave a Comment
Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <pre> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>
Trackback this post | Subscribe to the comments via RSS Feed
1.
valerie | July 21, 2008 at 3:10 am
hi thanks for this post, i googled and came to here, and its definitely what i wanted
2.
Kavin | October 14, 2008 at 6:57 pm
excellent. Eagerly awaiting the next post concerning the first name, middle name and surname!
cheers
3.
arindam | July 26, 2009 at 4:35 am
Thanx…but I have different need…say I need only numeric part of the below example of different size …how I get it by formula…
being cod collection recd agsnt 58502923264
58503408434 COD CASH SALES ANANYA RAY
being amt coll Agnst AWB No.58503409882
cod awb #58505010185.
being cod sales recd agsnt 58505010045
4.
rshewade | July 26, 2009 at 11:29 am
arindam,
your requirement is kind of rare and complicated…. but there is a way
you can create a user defined function which can check from 1st to last character and give you numeric item inside.
But yes you need to create a user defined function for it.