formula for name order switch that leaves empty cells blank

**redzan** · 05-16-2013, 02:13 PM

Name ordering

I use this formula to change first last to last, first:
=RIGHT(B3,LEN(B3)-SEARCH("#",SUBSTITUTE(B3," ","#",LEN(B3)-LEN(SUBSTITUTE(B3," ","")))))&", "&LEFT(B3,LEN(B3)-LEN(RIGHT(B3,LEN(B3)-SEARCH("#",SUBSTITUTE(B3," ","#",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))))-1)

I have another one to do the opposite (last, first to first last:
=TRIM(MID(E3,FIND(",",E3)+1,99))&" "&LEFT(E3,FIND(",",E3)-1)

I just copied those from other posts on here and they work fine. However, occasionally I will have a blank cell in the column so if I copy down those formulas the blank cells will return VALUE!. I don't want to see that. I want the cell to stay empty if the name is missing.

I have no idea how to write these myself so I just try why you guys post on here until I get what I want but can't find anything that doesn't leave me with VALUE! when the cell is blank. Hopefully this makes sense to someone and can help me out.

DaveDeV · 05-16-2013, 02:17 PM

Hi,

You could try using a wrap-around of =IFERROR(RIGHT......-1),"")

Dave

**MarvinP** · 05-16-2013, 02:21 PM

Hi redzan and welcome to the forum,

Excel (in the versions since 2003) has a function called "IFERROR" which is what I think you want. If you were using one of the 3 newer versions of Excel you would simply change your formulas to ....
= Your Formula ...... change it to the below.

= IfError( Your Formula, "")

Since you suggest you have 2003 Excel here is the method. http://tutorialsources.com/excel/tut...ction-help.htm

DaveDeV · 05-16-2013, 02:29 PM

Oops... Didn't spot the Excel 2003 caveat...

I seem to recall a logical function from 2003 which was "ISERROR" which could be used with an IF statement to return a "TRUE" or "FALSE" condition...

The construct used to be IF(ISERROR(TRUE,FALSE)) - I think.... This would appear to have been replaced by IFERROR as a more elegant approach

I can't test it as I no longer have access to Excel 2003

A-Ha! Found a site with the details (Microsoft would you believe?):

MS Support Post on ISERROR use to suppress error messages

To summarise from above:

Use =IF(ISERROR(Formula)," ",Formula)

The ISERROR refers to the first incidence of "Formula" and, based on result (error or no error) actions arguments 1 or 2 of the IF statement

Dave

**redzan** · 05-16-2013, 10:23 PM

hm...not working but maybe I'm just not entering it correctly. Will you double check these?

This is what I pasted in:
=IF(ISERROR(RIGHT(B3,LEN(B3)-SEARCH("#",SUBSTITUTE(B3," ","#",LEN(B3)-LEN(SUBSTITUTE(B3," ","")))))&", "&LEFT(B3,LEN(B3)-LEN(RIGHT(B3,LEN(B3)-SEARCH("#",SUBSTITUTE(B3," ","#",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))))-1))," ",RIGHT(B3,LEN(B3)-SEARCH("#",SUBSTITUTE(B3," ","#",LEN(B3)-LEN(SUBSTITUTE(B3," ","")))))&", "&LEFT(B3,LEN(B3)-LEN(RIGHT(B3,LEN(B3)-SEARCH("#",SUBSTITUTE(B3," ","#",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))))-1))

Also tried this on:
=IF(ISERROR(TRIM(MID(E3,FIND(",",E3)+1,99))&" "&LEFT(E3,FIND(",",E3)-1))," ",TRIM(MID(E3,FIND(",",E3)+1,99))&" "&LEFT(E3,FIND(",",E3)-1))

Thanks for your help.

**Tony Valko** · 05-16-2013, 10:34 PM

As long as the first name is a single word name (no Jim Bob Van Bundy type of names!):

A2: John Smith

=MID(A2&", "&A2,FIND(" ",A2)+1,LEN(A2)+1)

Returns: Smith, John

To account for an empty cell:

=IF(A2="","",MID(A2&", "&A2,FIND(" ",A2)+1,LEN(A2)+1))

**redzan** · 05-17-2013, 06:14 PM

Thank you Tony that worked great.

I need one to do the opposite now that ignores empty cells.

Example:
Smith, John

switch to:
John Smith

**Tony Valko** · 05-17-2013, 06:36 PM

Try this...

=IF(A2="","",MID(A2&" "&A2,FIND(",",A2)+2,LEN(A2)-1))

**redzan** · 05-17-2013, 06:51 PM

Thanks again Tony, just what I needed. I do wonder though if there is a way to properly move names with things like:

John Smith, Jr.

**Tony Valko** · 05-17-2013, 07:01 PM

Names and addresses are very difficult to parse.

Consider these examples:

Betty Jo Spickerman
Oscar De La Hoya

Those are real names.

Where does the first name end and the last name begin?

In our human minds we can easily tell the first name from the last name but how on earth do we get Excel to figure it out?