LinkBack URL
About LinkBacks
Looking for formula (Left, Right, Search, LEN, or SUBSTITUTE function) to split text in excel using formulas. See sample file. Desired outcome is in column B2:C5.
Thanks
Looking for formula (Left, Right, Search, LEN, or SUBSTITUTE function) to split text in excel using formulas. See sample file. Desired outcome is in column B2:C5.
Thanks
Last edited by bjnockle; 07-12-2021 at 10:02 PM.
1. It's always best to attach a sample file and to include any type of exception you can think of and the results (see yellow banner at the top of the page).
2. Are you sure you go that right? You just put a space between First and Name. Do you mean you want the First and last name from A2 and A3 into cell B2? And then you would want the first name from A4 and last name from A5 in cell B3?
Gregb11: Sample file attached. Desired outcome is in column B2:C5.
I don't get it.
In A2, if my first name is Thomas, how do you split that up into cells B2 and C2
and if my last name is Hillhouse in cell A3, what would you want in cells B3 and C3? Please use real (sample) names in your example sheet.
As Greg has implied, the sample data provided is utterly useless.
It is such hard work every time trying to get usable sample worksheets out of you - you make it so very hard for anyone trying to help you. You need to provide REALISTIC samples.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
You posted a similar request a few days ago, and even allowing for the crassness of the data, you had the answer to rows 4 and 5. Plus, given the number of posts from you over the years on this same subject, you should be able to solve these yourself.
Mmm ... I hadn't spotted the other similar thread. It's disappointing that no effort seems to be being made to learn from the help given over the years.
Gregb11: the actual texts I want to split are the four texts supplied in the sample file. I am not splitting anything else. Thus, what is contained in the sample file is my actual data, and the way it is lined up is how is it actually going to be lined up. Only need a formula to split them. Sorry if i did not properly explain this.Thanks
The point is if you have a string firstlast, how does Excel know that the answer is
first last and not
f irstlast or
fi rstlast or
fir stlast etc.
It can't.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh
AliGW: My intention is not to make things hard for anyone of you guys because I know how tirelessly you guys work to help folks like me with their needs on this thread. I think i need to do a great job of properly explaining my problem-solving need with a good sample file. Thanks
Glenn Kennedy: Gregb11: the actual texts I want to split are the four texts supplied in the sample file. I am not splitting anything else. Thus, what is contained in the sample file is my actual data, and the way it is lined up is how is it actually going to be lined up. Only need a formula to split them. Sorry if i did not properly explain this. Thanks
My reply vanished!!
If your string is FIRSTLAST, how can excel decide WHERE to put the break:
F IRSTLAST
FI RSTLAST
FIR STLAST
etc.
It can't.
OK, in B2 you could enter this:
=TRIM(LEFT(A2,LEN(A2)-4))
In C2, you could enter this:
=RIGHT(A2,4)
Copy both down.
So what you are telling us, then, is that FIRST, LAST and NAME do not stand for anything else.... the actual texts I want to split are the four texts supplied in the sample file. ... Thus, what is contained in the sample file is my actual data, and the way it is lined up is how is it actually going to be lined up.
Really???
Gregb11: great solution. Works like a charm. Thanks
AliGW: Yes, thanks
I'm flabbergasted! Why would you be dealing with a list of data like this? What's it for?
bjnockle, your question is not clear enough to understand what you want to achieve, but taken at face value it looks like you always want column C to always have the last 4 letters, and column B should have all the characters excluding the last 4. If that is the case, the formula you want in cell B2 isOriginally Posted by bjnockle
, and the formula you want in cell C2 isFormula:
Please Login or Register to view this content..Formula:Please Login or Register to view this content.
Please note that in the simple example I have shown there will be a trailing space in rows 4 and 5. If you want to trim the trailing space you can change B2 to. I have added TRIM in the attached spreadsheet.Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks