Vlookup functions which could not recognise similar alphanumerics

  • Thread starter Thread starter Shawn
  • Start date Start date
S

Shawn

I would like to find out why alphanumerics, e.g 14.02F100034 in separate
columns are not detected and hence "#N/A" was displayed. The whole worksheet
was copied and pasted from two separate worksheet and superimposed. I thought
that by formatting cells to GENERAL and pasting these data choosing PASTE
Specials with just Values Only would do the trick, and it failed. Please
advise what could be done to rectify the problems as the whole data contains
about more than 5000 rows of information.
 
Hi

Probably you have trailing or leading spaces in some cells. When spaces
aren't required in column, there is an easy way to get rid of abundant
ones - select the range with values, und use Replace feature to replace all
spaces with nothing (Search and Replace>Search= " ">Replace All)
 
I would like to find out why alphanumerics, e.g 14.02F100034 in separate
columns are not detected and hence "#N/A" was displayed. The whole worksheet
was copied and pasted from two separate worksheet and superimposed. I thought
that by formatting cells to GENERAL and pasting these data choosing PASTE
Specials with just Values Only would do the trick, and it failed. Please
advise what could be done to rectify the problems as the whole data contains
about more than 5000 rows of information.
Click to expand...


For vlookup, general cell type formatting is used, IIRC.

I have had "cut-n-paste" issues before with vlookups, and I always
resolved it by "playing" with cell data type formatting.
 
Back
Top