combining names

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
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!
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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>
kitten_smush2.jpg

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
=A1&" "&IF(LEN(B1),"& "&B1&" ","")&C1
This message was edited by Aladin Akyurek on 2002-10-04 18:53
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
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

New Member
Joined
Mar 10, 2002
Messages
23

ADVERTISEMENT

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:

Code:
 =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

Code:
 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.

Code:
 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

Active Member
Joined
Jan 13, 2002
Messages
424
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Active Member
Joined
Jan 13, 2002
Messages
424
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,413
Messages
5,595,998
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top