Concatenating names

[hhebe]

I create mailers at work and currently use the formula =IF(E2="",D2,CONCATENATE(D2&" & "&E2)). The first borrower's full name is in cell d2 and the co-borrower's full name (if there is one) is in e2.

For example

d2=John Smith e2=Jane Smith concatenated=John Smith & Jane Smith
d2=Bryan Boyd e2=Shelby Wright concatenated=Bryan Boyd & Shelby Wright
d2=Joe Jones e2=blank concatenated=Joe Jones

This works fine but I want to find a way to get rid of the last name for d2 if the last name is the same for both borrowers so that it would read Joe & Jane Smith.

I understand that I most likely will have to split the names so that first and last names are separated and that is okay.

Please help!

Thanks

 

[Excelestial]

=if(right(d2,len(d2)-search(" ",d2,1))=right(e2,len(e2)-search(" ",e2,1)),concatenate(left(d2,search(" ",d2,1)),", ",e2),concatenate(d2," ",e2))

 

[jeffreybrown]

How about...

=IF(E2="",D2,IF(RIGHT(D2,FIND(" ",D2))=RIGHT(E2,FIND(" ",E2)),LEFT(D2,FIND(" ",D2)-1)&" & "&E2,D2&" & "&E2))

 

[hhebe]

=if(right(d2,len(d2)-search(" ",d2,1))=right(e2,len(e2)-search(" ",e2,1)),concatenate(left(d2,search(" ",d2,1)),", ",e2),concatenate(d2," ",e2))

This is coming back with a comma between the names if the two lasts are the same so i substituted the comma with the ampersand in =IF(RIGHT(D2,LEN(D2)-SEARCH(" ",D2,1))=RIGHT(E2,LEN(E2)-SEARCH(" ",E2,1)),CONCATENATE(LEFT(D2,SEARCH(" ",D2,1)),"& ",E2),CONCATENATE(D2," ",E2))

but when the last names are different its coming back with a double space between the two peoples' names and no ampersand.

It also shows up with #VALUE! if there is no coborrower and I delete the blanks in the coborrowers column. If I don't delete the blanks it shows up with the borrower's name & (does the same thing with the formula I'm currently using so I delete (or clear contents) the blanks.

 

[hhebe]

How about...

=IF(E2="",D2,IF(RIGHT(D2,FIND(" ",D2))=RIGHT(E2,FIND(" ",E2)),LEFT(D2,FIND(" ",D2)-1)&" & "&E2,D2&" & "&E2))

This one just puts borrower full & coborrower full regardless of similarities...unless borrower and coborrower are exactly the same, then it will show (as example) Jacob & Jacob Reynolds.

 

[Excelestial]

=IF(E2="",D2,IF(RIGHT(D2,LEN(D2)-SEARCH(" ",D2,1))=RIGHT(E2,LEN(E2)-SEARCH(" ",E2,1)),CONCATENATE(LEFT(D2,SEARCH(" ",D2,1)),"& ",E2),CONCATENATE(trim(D2),"&",trim(E2)))

 

[jeffreybrown]

With your sample, what is not right about these results?

<TABLE style="WIDTH: 277pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=369 border=0><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2560" width=80><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3104" width=97><COL style="WIDTH: 144pt; mso-width-source: userset; mso-width-alt: 6144" width=192><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=80 height=21>John Smith</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=97>Jane Smith</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 144pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=192>John & Jane Smith</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Bryan Boyd</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Shelby Wright</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Bryan Boyd & Shelby Wright</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Joe Jones</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Joe Jones</TD></TR></TBODY></TABLE>

 

[hhebe]

=IF(E2="",D2,IF(RIGHT(D2,LEN(D2)-SEARCH(" ",D2,1))=RIGHT(E2,LEN(E2)-SEARCH(" ",E2,1)),CONCATENATE(LEFT(D2,SEARCH(" ",D2,1)),"& ",E2),CONCATENATE(trim(D2),"&",trim(E2)))

I had to add in spaces around the last ampersand (and excel added a missing closing parentheses at the end) but otherwise worked perfectly!


Thank you so much, both of you!

 

[Excelestial]

Quite welcome!

 

[hhebe]

With your sample, what is not right about these results?

<TABLE style="WIDTH: 277pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=369 border=0><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2560" width=80><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3104" width=97><COL style="WIDTH: 144pt; mso-width-source: userset; mso-width-alt: 6144" width=192><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=80 height=21>John Smith</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=97>Jane Smith</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 144pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=192>John & Jane Smith</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Bryan Boyd</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Shelby Wright</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Bryan Boyd & Shelby Wright</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Joe Jones</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Joe Jones</TD></TR></TBODY></TABLE>

These results are what I wanted but not what came through on the excel spreadsheet. Excel reads:

<TABLE style="WIDTH: 277pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=369 border=0><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2560" width=80><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3104" width=97><COL style="WIDTH: 144pt; mso-width-source: userset; mso-width-alt: 6144" width=192><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=80 height=21>John Smith</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=97>Jane Smith</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 144pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=192>John Smith & Jane Smith</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Bryan Boyd</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Shelby Wright</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Bryan Boyd & Shelby Wright</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Joe Jones</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Joe Jones &</TD></TR></TBODY></TABLE>

the & after Joe Jones is an easy fix by clearing the blank cell.