Concatenate Problems!!!

[Nicola]

Hi

I am currently trying to put together a database. Column C
contains the persons title i.e. Mr, Column D contains
their christian name i.e. John and Column E contains their
surname i.e. Smith. I have concatenating these into
Column F so that there are spaces between i.e. Mr John
Smith.

All was going swimmingly until i realised that in some
rows there isnt any data in Columns C & D and that Column
E contained Managing Director. When concatenating these
cells Excel puts a space before Managing Director
i.e. " Managing Director".

Has anyone any ideas as to the formula i can use to stop
this happening?

Many thanks

Nicola

 

[Ken Wright]

Assuming you are using something like the following to combine your fields:-

=C1&" "&D1&" "&E1 - Or you may be using the CONCATENATE function, eg

=CONCATENATE(C1," ",D1," ",E1)

Then simply expand either to inlcude the TRIM function, which will clear up any rogue
leading/trailing spaces:-

=TRIM(C1&" "&D1&" "&E1)

or

=TRIM(CONCATENATE(C1," ",D1," ",E1))

 

[Nicola]

Thanks for that Ken, it did the trick - so simple, yet it
has taken me all afternoon to resolve!

 

[Ken Wright]

LOL - My pleasure, and glad you got sorted.