combining names

[imported_unknown]

I have three columns: A always has a first name (such as Bob), B sometimes has a name (such as Carol) but is sometimes blank, C always has a last name (such as Smith).

I need to combine these three columns into a full name in new column:
1. If B has a name, add an ' & ' to the end of the name in A and combine all three columns to read - Bob & Carol Smith
2. If B is blank, combine A and C to read - Bob Smith

I was given the following formula:
=A1&" & "&IF(ISBLANK(B1),"",B1&" ")&C1

This works very well (and I thank whoever it was very, very much, however, if B is blank it is still inserting an '&' after the name in A - such as - Bob & Smith.

How do I alter this formula to get rid of the & in the event that B is blank.

Also, I now am done with columns A,B and C and only need to keep the result but Excel is not letting me get rid of them giving me something like a 'ref' message.

How can I get rid of these columns once I'm finished.

This very green novice to Excel thanks you very much - maybe I can stop ripping my hair out in frustration now!

Thanks!

 

[Von Pookie]

You're getting the #REF error because if you delete those first 3 columns, you have nothing for the new column to combine, so that formula you're using does nothing.

You could just hide the 3 columns you don't need. Select the entire columns, then right click in your selection and choose "hide."

They won't show up on the screen anymore, and your combining formula will still work.

_________________<font color="#3399FF" size="3">Kristy</font>

This message was edited by Von Pookie on 2002-10-04 16:13

 

[Aladin Akyurek]

=A1&" "&IF(LEN(B1),"& "&B1&" ","")&C1
This message was edited by Aladin Akyurek on 2002-10-04 18:53

 

[Richie(UK)]

Hi welcomelist,

How about: =A2&IF(ISBLANK(B2)," "," & ")&IF(ISBLANK(B2),"",B2&" ")&C2

Then copy the used range in columnC and pastespecial/values to columnD, then delete columns A to C.

HTH

 

[g_erhard]

Here's a formula that should do it.


Assuming column A contains your First Name, B contains the (possibly blank) Middle Name, and C contains the Last Name:

 =TRIM(A2 & " " &B2 & " " & C2)

The idea is to concatenate all the cells that make up the name (as many as you like, really) along with 'padding' spcaes between them, then use the TRIM function to strip out any leading, trailing, or 'extra' spaces in the resulting string. TRIM allows for one space between words, that's it. So it works great for cleaning up assembled strings where some elements may be blank.

EEK! back on edit... didna realize you were dealing with possible second names, rather than a middle name. My bad. Here's the updated equations (2 of them):

Column A = Primary First Name
John
john


Column B = Secondary First Name (may be blank)
Jane

Column C = Last Name
Yaya
smallberries

 Column D =IF(ISBLANK(B2), A2, A2 & " & " & B2)

John & Jane
john

This creates a "First Names" string, either it puts both names with an & between, or it copies just the first name.

 Column E =PROPER(TRIM(D2 & " " & C2))

John & Jane Yaya
John Smallberries


This pulls together the first names results with the last name, cleans up any spaces that may have been missed, and also enforces Proper Case, where all words have initial caps.

Better? (sorry about the misunderstanding on the requirements).
This message was edited by g_erhard on 2002-10-04 16:46

 

[imported_unknown]

thanks Richie - the problem is that there are actually two names (husband in A and wife's name -sometimes in B). When there is a wife's name in B, I need to add an "&" in there to indicate Bob and Carol.

I like the trim though - it works pretty easily.

 

[Aladin Akyurek]

On 2002-10-04 16:34, welcomelist wrote:
thanks Richie - the problem is that there are actually two names (husband in A and wife's name -sometimes in B). When there is a wife's name in B, I need to add an "&" in there to indicate Bob and Carol.

I like the trim though - it works pretty easily.

Did you try what I suggested?

 

[imported_unknown]

yes - bingo - it worked absolutely perfectly. Thank you so much. I ran out of time yesterday but was going to try to find out how to say 'thank you' in Dutch (assuming you are Dutch) but I'll just say it in English -

Thank you! You've solved my problem. Headache gone!