Getting annoying space after numbers on vlookup

[DPChristman]

I have a unique situation.

I have a spreadsheet with over 1,200 logins to CCTV systems across the US.

I built a simple lookup file so that you can place the location number in and you will get the IP/ID address to copy and place into the third party CCTV monitoring system.

The formula works, but when I try to copy and paste the IP/ID number, it adds a space after the number.

The third party software does not recognize the space, so you can't login to the CCTV system.

I can just tell the users to back out the blank space, but is there a way to have the blank space not be there in first place?

 

[duggie33]

You could try wrapping your VLOOKUP in TRIM to see if that removes the space.

=TRIM(VLOOKUP(.......))

Hope that helps,

Doug

 

[DPChristman]

You could try wrapping your VLOOKUP in TRIM to see if that removes the space.

=TRIM(VLOOKUP(.......))

Hope that helps,

Doug

Thanks. Unfortunately, it didn't help.

 

[BigBeachBananas]

Thanks. Unfortunately, it didn't help.

It would be helpful if you can share some examples of the current outputs and desired outputs.

 

[duggie33]

It must be something other than a space then. You could try wrapping it with CLEAN to see if that works.

Please provide some sample data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data, allowing us to copy/paste it to our Excel spreadsheets to work with the same data you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that this board also has a "Test Here” forum. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[DPChristman]

It would be helpful if you can share some examples of the current outputs and desired outputs.

The desired output is an 11 to 13 digit ID/IP Address, EXAMPLE: SHDU3HSJH54Z. This is pulled via a vlookup formula that pulls the data from a list when you enter the location number. The vlookup works fine, but is is the copy and paste where it falls apart. What is interesting is the original list exists in Google Docs. I just copied and pasted the values only into an excel spreadsheet. There is no problem with the cut and paste from the Google Docs spreadsheet. It is only when you copy and paste from excel. I am wondering if this could be a format issue. If I change the format where the codes are to text, hmmm? I will try that too.

 

[BigBeachBananas]

The desired output is an 11 to 13 digit ID/IP Address, EXAMPLE: SHDU3HSJH54Z. This is pulled via a vlookup formula that pulls the data from a list when you enter the location number. The vlookup works fine, but is is the copy and paste where it falls apart. What is interesting is the original list exists in Google Docs. I just copied and pasted the values only into an excel spreadsheet. There is no problem with the cut and paste from the Google Docs spreadsheet. It is only when you copy and paste from excel. I am wondering if this could be a format issue. If I change the format where the codes are to text, hmmm? I will try that too.

Try this formula on one of the 11-digit outputs and see if there are in fact 11 characters.

=LEN(A1)

 

[BigBeachBananas]

Also, try this link.

 

[MARK858]

As suggested in the other posts if you can use XL2BB to post a sample we can probably see what the hidden character is so it can be removed.