Separate Salutation and Name

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.

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)

Where have you given us a list of all salutations used?

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).

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.

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.