Forum Discussion

Copper Contributor

Jan 27, 2023

Solved

edit cells to add trailing space

I have numbers in column. In order for the VLOOKUP to work, there needs to be a space at then end of the numbers in the column. Since I am working with 5k lines of data, individually updating them is...

excel

HansVogelaar
Jan 27, 2023
cc2431

So the lookup value has a trailing space but the first column of the lookup range does not?

If that is correct:

Let's say your formula looks like =VLOOKUP(B2, lookup_range, 2, FALSE)

You can change that to =VLOOKUP(--B2, lookup_range, 2, FALSE)

That should take care of the trailing space...

cc2431

Copper Contributor

Jan 27, 2023

HansVogelaar Iam pulling data from two tools and exporting the file to multiple spreadsheets. The source file that I am basing my VLOOK up on is fine( it is a microsoft tool). The second tool ASTRA when extracting the data into Excel( or CSV) does not include a space at the end key field. 1234 vs 1234( blank at the end). The VLOOKUP does not find a hit unless I add a space at the end of the 1234.

HansVogelaar

MVP

Jan 27, 2023

cc2431

So the lookup value has a trailing space but the first column of the lookup range does not?

If that is correct:

Let's say your formula looks like =VLOOKUP(B2, lookup_range, 2, FALSE)

You can change that to =VLOOKUP(--B2, lookup_range, 2, FALSE)

That should take care of the trailing space...

cc2431
Copper Contributor
Jan 27, 2023
Excellent. I'll give that a try. Thank you!