Failure doing VLOOKUP, Due to space in beginning and end of cells.

[saumyamukherjeee]

Good Morning and greetings for the day.


Recently I've been working on a project where I've to do a vlookup from a series of 8digits of no. say row A1 with a data. Now the problem is I've copies and pasted numbers from different sources and some space is appearing in some cases.


for eg.

A1

12345678

87533839

12345664

64547903

65796346

94746272


Now if you look carefully there are spaces in front of 87533839 and 94746272.


While doing vlookup excel is automatically skipping these cells and results in #N/A error.


Advised solution -


1. Text to column feature - I've tried the convert the column cell to text ( ALT+D+F+F+ENTER+ENTER+ENTER ) But it didn't work for all the cells, God knows what is the issue.


2. TRIM - I've used TRIM Formula { =TRIM(A1:AN) } Well some cases got rectified in this case but it also skipped a dozen of cells.


May i ask all excel ninjas if any other alternative solution to this query ?


Thanks...Saumya

 

[Hui]

Saumya


Try using the Clean() function as well

 

[nazmul_muneer]

@saumyamukherjeee

You used TRIM function. It cut off extra spaces. Exra space means

If it got more than one space it keeps one space.

If it got any space at the starting place of a cell value it removes.


You can use CLEAN and SUBSTITUTE function to eliminate all spaces and unseen texts.


Regards,


Muneer

 

[shrivallabha]

Maybe you should take a look at this thread:


Use formula posted by Deb to clean up the characters and then do VLOOLUP.

http://chandoo.org/forums/topic/invisible-character

 

[saumyamukherjeee]

Hui Thanks a lot, Clean worked smoothly.


Muneer, Thanks.. TRIM i've used earlier also and using CLEAN and SUBSTITUTE is good


Shrivallabha, Thanks a lot.


@all above, thanks a ton for help, you all rock!!

 

[SirJB7]

Hi, saumyamukherjeee!

May I suggest you to change your nick name? It's too long and it overlaps the comment area making it unreadable.

Regards!

 

[keymaster]

If you do not want to clean your source data, you can also try using *vlookup value, like this:


=vlookup("*"&value-to-look, range, column, false)


This will ignore any spaces. For more on using such wildcards, refer to http://chandoo.org/wp/2010/11/01/using-wildcards-with-vlookup/

 

[saumyamukherjeee]

Chandoo, let me try this too.. anyways if u're suggesting it must work since u're excel God ( That's what I discuss with all my friends and colleagues discussing tricks suggested by you )


SirJB7, Hope now u're comfortable with my name