vlookup fails due to trailing spaces

[bushyiii]

Every item in my 24k long Array is padded with trailing spaces such that each element is 50 characters wide, the actual visible text in the Array is usually much less.This is a result of the way the Array was created. The Search Criteria is the same length as the visible text in the Array. As a result of the disparity in their respective lengths Vlookup fails.

I don’t know how to clean up (remove) the training spaces other than manually, is there any other solution to deal with this problem?

Here’s an example of a failure using vlookup function in column C =VLOOKUP(A1,B$1:B$4,1,1 )

001Trolling.2017.WEBRip.mp4	001Trolling.2017.WEBRip.mp4   	#N/A

This is after removing the not visible spaces from column B

001Trolling.2017.WEBRip.mp4	001Trolling.2017.WEBRip.mp4	001Trolling.2017.WEBRip.mp4

[robleyd]

Perhaps the TRIM() function might help you?

[bushyiii]

I forgot about TRIM thanks fr the suggestion.

[bushyiii]

Trim was exactly what I needed. Thanks you

[erAck]

Select the data column you want to clean and then use Edit → Find and Replace… (Ctrl+H) with a regular expression to find trailing blanks.

• Find: +$ (note there is a leading blank before the +)
• Replace: (leave this empty to replace the match with nothing)
• Other options:
• Current selection only
• Regular expressions

[bushyiii]

I don’t understand this suggestion but will make a copy of the spreadsheet and give it a try, thanks.

[keme]

As @robleyd commented, using the TRIM() function in a new column to create trimmed down data may be an option. This new column may be then used directly as the lookup key, or you can copy that and “paste special” (values only) to have trimmed data in the original column. Note that TRIM() will also remove leading spaces (likely desirable) and internal double spaces (which you may want to retain).

A different approach would be to add the padding in the lookup term. Something like

=VLOOKUP(A1&REPT(" ";50-LEN(A1));B$1:B$4;1;1)

This will not change source data, so it should also work straight away if some time you need to recreate the Array. Not as clean as the regex find/replace or TRIM() approaches, though.

[bushyiii]

That’s an interesting suggestion, I am a noob so it will take me a while to get my head around it.