Ampersand creating extra space when last, first and middle intitial is combined.

[Tara Zuelke]

I am connecting employee id numbers when I copy and paste data from programs at work.  I had to use ampersand to conform to the data I pulled from to get the id numbers.  This worked for all the drivers with a middle intitial but the drivers that do not have a middle initial the id does not connect to them.  It is due to that fact that there is an extra space.  I can add a space in the copied name and it will pull up.  However, this will be confussing to other people using this program.  How do I get around this issue?

[#2]

Hello,

it would be helpful if you could post your formula and a data sample and specify what the desired result should be. 

In the screenshot below, this is the formula

=TRIM(IF(LEN([@first])>0,[@first],"")&IF(LEN([@middle])>0," "&[@middle],"")&IF(LEN([@last])>0," "&[@last],""))

It does not duplicate blanks and leaves no leading or trailing blanks.

If you don't use a table with structured references, you can of course replace the column names with A2, B2 and C2, etc. then copy down.

Does that help?

[#3]

I used the example you gave and most of the id numbers pop up but the same two will not.  I was able to get them by manuelly adding a space at the end of the name.  Very frustrated.  I am also having issues with the multiple names.  Need to pull all employee engine checks, post-trips, swipes, student checks into form.  However, vlook up only does the first duplicate and skips the rest.   

[#4]

how do you upload files?

[#5]

Hello,

where does Vlookup come in? Your question is about combining first, middle and last name without any extra spaces. Are you then using the result of the concatenation in a Vlookup? Please post a data sample, but please make sure these are not real names. Make up some fake ones to protect people's privacy.

You can upload a file by clicking the button "Choose Files" that appears when you write a post or a reply.

[#6]

Trying again....seems I am having issues because when I choose the file and send it does not go through.

[#7]

Ehhmmm, you posted a workbook with 8 sheets. It would be great if you could post some information about where the problem is.

Ideally, a data sample consists of the original data, a mock up of the desired results, and then some words that explain the logic for the expected result. 

Can you explain or mock up what you would like to achieve in the context of your workbook, and use sheet names and cell addresses to help me find my way around your file?

[#8]

do you still need help?