Vlookup functions which could not recognise similar alphanumerics

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shawn - 20 May 2008 07:15 GMT

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.

Reply to this Message

Arvi Laanemets - 20 May 2008 08:17 GMT

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)

Signature

Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

>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
[quoted text clipped - 6 lines]
> contains
> about more than 5000 rows of information.

Reply to this Message

AnimalMagic - 30 Jul 2008 01:39 GMT

>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
[quoted text clipped - 3 lines]
>advise what could be done to rectify the problems as the whole data contains
>about more than 5000 rows of information.

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.

Reply to this Message