Concatenate formula questions about First Name Last Name and spaces not matching

[purplesasha]

I have a spreadsheet containing 3 columns, First Name, Nickname and Last Name.

In a separate column, I concatenate First Name and Last Name as =PROPER(D2&" "&F2) to return the following:

Jane Doe

It appears that there are many spaces between between First Name and Last Name even though the formula only indicates one space.

I copy the entire column from that spreadsheet to another spreadsheet in which I want to use the =MATCH(E2,A$2:A$427,0) formula, but it doesn't work with the column I am comparing because the name there displays as:

Jane Doe

It does not work unless I retype the name manually without the CONCATENATE formula and there are 400+ names on the spreadsheet to compare to the master list of names I want to concatenate.

I am not sure why the values are not matching up

 

[Weazel]

you could try....

=TRIM(PROPER(D2&" "&F2))

 

[raj2206]

Hi purplesasha,

You can also use =proper(trim(clean(First_Name)&" "&clean(Last_Name)))

This will remove all the spaces and not printable characters in the cell since many a times it appears that there is a space in the cell but actually they may be some other special characters which could be removed by the clean function.

Thanks/Raj