Hi,
In the attached demo file I am trying to seperate title and name
I mean in Column B, I need CAPT, MR,. MRS Dr etc and in column C the name
How do i do this
Hi,
In the attached demo file I am trying to seperate title and name
I mean in Column B, I need CAPT, MR,. MRS Dr etc and in column C the name
How do i do this
Please supply a list of all possible salutations and a sample sheet that meets our guidelines (see yellow banner at the top of the screen), not 3000 rows.
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
Can't be done in the B column, but in C3 copied down:
=LEFT(B3,FIND(" ",B3)-1)
and in D3 copied down:
=MID(B3,FIND(" ",B3)+1,99)
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.
Attached Please
Where have you given us a list of all salutations used?
It is in Column D please
No. Glenn asked for a single unique list of salutations used. Some of the salutations in column D are not correct (e.g. SQN LDR is showing just as SQN).
Last edited by AliGW; 02-11-2022 at 04:05 AM. Reason: Typo fixed.
1. The sample is still 3000 rows.
2. That's not really a list, is it? And it's not even correct.
Here is a SAMPLE sheet.
3. Named range "Ranks" - CTRL- F3 to view/edit. Add more ranks, the range will automatically adjust.:
=Sheet2!$K$1:INDEX(Sheet2!$K:$K,MATCH("Zzzz",Sheet2!$K:$K))
4. To extract ranks:
=IFERROR(LOOKUP(1000,SEARCH(" "&Ranks," "&C3),Ranks),"")
5. To extract the rest:
=TRIM(SUBSTITUTE(C3,D3,"",1))
Next time PLEASE make sure you include a SAMPLE sheet, not thousands of rows.
or, with another Named Range to remove the inconsitency in the results supplied in column C.
Thank You so much works good
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks