Need help with first and last name columns

[neilexcel]

Guys need help merging contacts. So I have column A which contains First name, Column B contain Last Name. However Column B also contain "N/A", if there is no last name. Can someone direct me to do the following, I want to merge the Column to A only if Column B has a name and not "N/A". Hope this make sense, any help will be appreciated.

 

[3link]

Neil,

Assuming you want to make a third column with first and last,

=IFERROR(A1&" "&B1, A1)

This assumes that your list starts with row 1. If column B has NA(), only the first name will be displayed.


Alternatively, this would leave a blank instead of the first name when B = NA

=IFERROR(A1&" "&B1, "")

 

[GK039]

If you mean that you want to concatenate the two values, then in a third column try:

=IF(NOT(ISNA(B1)),A1&" "&B1,"")

Hope this helps you

George

Guys need help merging contacts. So I have column A which contains First name, Column B contain Last Name. However Column B also contain "N/A", if there is no last name. Can someone direct me to do the following, I want to merge the Column to A only if Column B has a name and not "N/A". Hope this make sense, any help will be appreciated.

 

[neilexcel]

Thanks for the help, here is what I'm trying to do

Column A Column B
<table border="0" cellpadding="0" cellspacing="0" width="302"><col style="width: 82pt;" width="109"> <col style="width: 145pt;" width="193"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl66" style="height: 13.5pt; width: 82pt;" height="18" width="109">Dave Mefford</td> <td class="xl65" style="border-left: medium none; width: 145pt;" width="193"> N/A</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl65" style="height: 13.5pt; border-top: medium none; width: 82pt;" height="18" width="109">Didier Charreton</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193"> N/A</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl66" style="height: 13.5pt; border-top: medium none; width: 82pt;" height="18" width="109">Ed Roper</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193"> N/A</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl66" style="height: 13.5pt; border-top: medium none; width: 82pt;" height="18" width="109">Dave Mefford</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193"> N/A</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl64" style="height: 13.5pt; border-top: medium none; width: 82pt;" height="18" width="109">David</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193"> Barlaam</td> </tr> </tbody></table>
As you can see some of the Column A already has First and Last name, and some cell don't example "David". I want to combine the last name (column B) to column A (or a new column) only if there is a last name and omit "N/A". George your formula worked some what, but it included the "N/A" in the new column.

 

[GK039]

Try this:

=IF(NOT(ISNA(B7)),A7&" "&B7,A7)

It will give you the value from column A if there is already a full name otherwise it will concatenate the name with the surname, if they are in separate columns

Thanks for the help, here is what I'm trying to do

Column A Column B
<table width="302" border="0" cellpadding="0" cellspacing="0"><col style="width: 82pt;" width="109"> <col style="width: 145pt;" width="193"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl66" style="height: 13.5pt; width: 82pt;" width="109" height="18">Dave Mefford</td> <td class="xl65" style="border-left: medium none; width: 145pt;" width="193"> N/A</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl65" style="height: 13.5pt; border-top: medium none; width: 82pt;" width="109" height="18">Didier Charreton</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193"> N/A</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl66" style="height: 13.5pt; border-top: medium none; width: 82pt;" width="109" height="18">Ed Roper</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193"> N/A</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl66" style="height: 13.5pt; border-top: medium none; width: 82pt;" width="109" height="18">Dave Mefford</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193"> N/A</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl64" style="height: 13.5pt; border-top: medium none; width: 82pt;" width="109" height="18">David</td> <td class="xl64" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193"> Barlaam</td> </tr> </tbody></table>
As you can see some of the Column A already has First and Last name, and some cell don't example "David". I want to combine the last name (column B) to column A (or a new column) only if there is a last name and omit "N/A". George your formula worked some what, but it included the "N/A" in the new column.

 

[neilexcel]

Thanks George,

It's working, but it's bringing the "N/A" if it the column has both the first and last name. See the output

column A Column B Column C

<table border="0" cellpadding="0" cellspacing="0" width="495"><col style="width: 82pt;" width="109"> <col style="width: 145pt;" span="2" width="193"> <tbody><tr style="height: 67.5pt;" height="90"> <td class="xl66" style="height: 67.5pt; width: 82pt;" height="90" width="109">Aaron York</td> <td class="xl66" style="border-left: medium none; width: 145pt;" width="193">N/A</td> <td class="xl66" style="border-left: medium none; width: 145pt;" width="193">Aaron York N/A</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl65" style="height: 13.5pt; border-top: medium none; width: 82pt;" height="18" width="109">Aaron York</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">N/A</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Aaron York N/A</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl65" style="height: 13.5pt; border-top: medium none; width: 82pt;" height="18" width="109">Abby</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Piazza</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Abby Piazza</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl68" style="height: 13.5pt; border-top: medium none; width: 82pt;" height="18" width="109">Abdullah</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Ertekin </td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Abdullah Ertekin </td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl68" style="height: 13.5pt; border-top: medium none; width: 82pt;" height="18" width="109">Abdullah</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Khan</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Abdullah Khan</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl68" style="height: 13.5pt; border-top: medium none; width: 82pt;" height="18" width="109">Abdullah</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Khan</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Abdullah Khan</td> </tr> </tbody></table>


<table border="0" cellpadding="0" cellspacing="0" width="495"><col style="width: 82pt;" width="109"> <col style="width: 145pt;" span="2" width="193"> <tbody> <tr style="height: 13.5pt;" height="18"> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">
</td> </tr> </tbody></table>

 

[GK039]

This N/A is a plain text of the error #N/A?

Thanks George,

It's working, but it's bringing the "N/A" if it the column has both the first and last name. See the output

column A Column B Column C

<table width="495" border="0" cellpadding="0" cellspacing="0"><col style="width: 82pt;" width="109"> <col style="width: 145pt;" width="193" span="2"> <tbody><tr style="height: 67.5pt;" height="90"> <td class="xl66" style="height: 67.5pt; width: 82pt;" width="109" height="90">Aaron York</td> <td class="xl66" style="border-left: medium none; width: 145pt;" width="193">N/A</td> <td class="xl66" style="border-left: medium none; width: 145pt;" width="193">Aaron York N/A</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl65" style="height: 13.5pt; border-top: medium none; width: 82pt;" width="109" height="18">Aaron York</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">N/A</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Aaron York N/A</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl65" style="height: 13.5pt; border-top: medium none; width: 82pt;" width="109" height="18">Abby</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Piazza</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Abby Piazza</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl68" style="height: 13.5pt; border-top: medium none; width: 82pt;" width="109" height="18">Abdullah</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Ertekin </td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Abdullah Ertekin </td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl68" style="height: 13.5pt; border-top: medium none; width: 82pt;" width="109" height="18">Abdullah</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Khan</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Abdullah Khan</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl68" style="height: 13.5pt; border-top: medium none; width: 82pt;" width="109" height="18">Abdullah</td> <td class="xl68" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Khan</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">Abdullah Khan</td> </tr> </tbody></table>


<table width="495" border="0" cellpadding="0" cellspacing="0"><col style="width: 82pt;" width="109"> <col style="width: 145pt;" width="193" span="2"> <tbody> <tr style="height: 13.5pt;" height="18"> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 145pt;" width="193">
</td> </tr> </tbody></table>

 

[neilexcel]

No, "N/A" is just a text for last name, if there is was no last name defined.

 

[GK039]

This will do it then

=IF(B1="N/A",A1,A1&" "&B1)

No, "N/A" is just a text for last name, if there is was no last name defined.

 

[neilexcel]

Thank you, Thank you, Thank you George. You saved me a lot of time. Thank you!