Splitting names

**rwl518p** · 09-04-2012, 03:03 PM

Hi

I have written the following formula to split a name field into first and last names. It also has an error check where the last name only is given.

It all works perfectly when there is a first and last name, but it misses off the first character when there is a surname only.

Can anyone help please?

=UPPER(IF(ISERROR(FIND(" ",A1,1)),LEFT(A1,FIND("",A1,1)-1),LEFT(A1,FIND(" ",A1,1))))

The above formula is used to find the leftmost character before the space and works perfectly every time.

=UPPER(IF(ISERROR(FIND(" ",Q3,1)),RIGHT(Q3,LEN(Q3)-FIND("",Q3,1)),RIGHT(Q3,LEN(Q3)-FIND(" ",Q3,1)-0)))

This formula works perfectly where there is a first name, but always returns the last name minus the first character where there is only a last name given.

I have attached a spreadsheet showing this.

Thanks

Ken

**ChemistB** · 09-04-2012, 03:08 PM

If there are no spaces, then you want to bring the entire "name" into Col D so the formula in D should be

=UPPER(IF(ISERROR(FIND(" ",A1,1)),A1,RIGHT(A1,LEN(A1)-FIND(" ",A1,1)-0)))

Does that work for you?

**ajm123456789** · 09-04-2012, 03:12 PM

=UPPER(IF(ISERROR(FIND(" ",A1,1)),RIGHT(A1,LEN(A1)-FIND("",A1,1)+1),RIGHT(A1,LEN(A1)-FIND(" ",A1,1)-0)))does this work for you?

**rwl518p** · 09-04-2012, 04:00 PM

SOLVED

Thanks guys

**Cutter** · 09-04-2012, 05:23 PM

@ rwl518p

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.