Combine firstt & last name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jennie - 08 Jun 2007 22:50 GMT

I ahve a spreadsheet were the first name and last name are in sperate colums.
I can join combine the two and copy as a value. When I combine them though
there are several extra spaces. Does anyone know what causes this and how to
fix it? here is an example:

John Smith shows as John Smith

Reply to this Message

Mike G - 08 Jun 2007 22:57 GMT

Trim function

>I ahve a spreadsheet were the first name and last name are in sperate
>colums.
[quoted text clipped - 4 lines]
>
> John Smith shows as John Smith

Reply to this Message

Teethless mama - 08 Jun 2007 23:00 GMT

=TRIM(A1&" "&B1)

> I ahve a spreadsheet were the first name and last name are in sperate colums.
> I can join combine the two and copy as a value. When I combine them though
> there are several extra spaces. Does anyone know what causes this and how to
> fix it? here is an example:
>
> John Smith shows as John Smith

Reply to this Message

Gord Dibben - 08 Jun 2007 23:33 GMT

How are you joining/combining the two names?

=A1 & B1

=CONCATENATE(A1,A2)

Either method will produce spaces if you have extra spaces in the cell values.

Try =TRIM(A1 & " " & B2) to return John Smith

Gord Dibben MS Excel MVP

>I ahve a spreadsheet were the first name and last name are in sperate colums.
>I can join combine the two and copy as a value. When I combine them though
>there are several extra spaces. Does anyone know what causes this and how to
>fix it? here is an example:
>
>John Smith shows as John Smith

Reply to this Message

Mike G - 09 Jun 2007 02:23 GMT

The trim function does not neet the extra &" " in between. The definition
is: removes all spaces from a text string except for a single space between
words. Of course if you need more than one, then you would use " " but it
looks like the OP was looking for one, I am using 2007 version, don't
remember if other versions were different. So =TRIM(a1&b1)

> How are you joining/combining the two names?
>
[quoted text clipped - 21 lines]
>>
>>John Smith shows as John Smith

Reply to this Message

Gord Dibben - 09 Jun 2007 02:51 GMT

Thanks Mike

Either formula produces a single spaced John Smith in Excel 2003

Yours is shorter<g>

Gord

>The trim function does not neet the extra &" " in between. The definition
>is: removes all spaces from a text string except for a single space between
[quoted text clipped - 27 lines]
>>>
>>>John Smith shows as John Smith

Reply to this Message