Forum Discussion
Ampersand creating extra space when last, first and middle intitial is combined.
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?
7 Replies
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?
how do you upload files?
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.
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.
