Help eliminating a space in concatenate formula

I have the following formula in a cell in Excel
=CONCATENATE(BC2," ",BN2," ",BB2,","," ",BO2,"")
The result would normally be Bob N. Reynolds
or Bob Reynolds

My question is how do I remove the extra space when there is no middle
initial in cell bb2 so it will read
Bob Reynolds with one space.

Thanks so muych
Bob

Hi Bob,

Try:

= Trim(CONCATENATE(BC2," ",BN2," ",BB2,","," ",BO2,""))

---
Regards,
Norman



"Bob Reynolds" <rjr1040@bellsouthReMoVeThIsFiRsT.net> wrote in message
news:wymle.21264$8S5.9381@bignews3.bellsouth.net...
>I have the following formula in a cell in Excel
> =CONCATENATE(BC2," ",BN2," ",BB2,","," ",BO2,"")
> The result would normally be Bob N. Reynolds
> or Bob Reynolds
>
> My question is how do I remove the extra space when there is no middle
> initial in cell bb2 so it will read
> Bob Reynolds with one space.
>
> Thanks so muych
> Bob
>
>

use an if statement to look at the cell containing the middle names, if
the cell is blank, then just concatenate forename and surname, else
concatenate forename middle name and surname.
if your data is set up with forename in BC, middlename in BN and
surname in BB, then formula would be
=IF(BN2="",CONCATENATE(BC2," "," ",BB2,",","
",BO2,""),CONCATENATE(BC2," ",BN2," ",BB2,","," ",BO2,""))

not sure if i have guessed your colums correctly, but i hope u get my
drift.

and now time to go home!!