VLOOKUP WITH ALPHA NUMERIC

Hi VG,

pls. explain how you get your data into excel, what is the database,
what is the file you get, how do you import into excel. the best is to
solve the problem already here at this stage.

arno

Hi again VG,

some are pure numbers and others are alpha-numeric, for

no, they are all alpha-numeric, just excel treats them as it likes.


I have no control over what format
these come into Excel as.

maybe you have - in the exportfunction of your database or in the
import to excel.

All of the numerics sort separately from
the alpha-numerics, so my vlookup formula won't work.

this is the problem. there are workarounds for this - in the vlookup
formula like KL suggested or you correct the data eg. with a formula.
Eg. if you have your alphanumeric data in column A you could use this
formula in column B (and copy down to the end): ="'"&A1 this will make
'123 (which is a text, the '-character is invisible but defines the
content as text) out of 123 (which is a number). Then you could copy
column B and PasteSpecial/VALUES to column A - this will overwrite your
mixed numbers/text with only texts from col B. You could have macros
doing this for you.

THEN
RETYPE THE VALUE IN THE CELL

no way

arno

Arno - it turns out one of your first suggestions saved me - I did the =a1=b1
check (such a simple thing, but I never knew of it before!!), and found out
that my two sets of alpha-numeric data were actually different, so the
vlookups couldn't match. In one set, which comes from an extract to a .csv
file that is comma delimited (written by our programmers), the code was
actually "6103D". In my vlookup table (which came from another extract of
codes from our "vanilla" accounting system, i.e. we didn't program it), the
code was "6103D " - with a space at the end. This is because the length of
the field is 6 characters, and this code is only 5 characters - well it turns
out all of the less than 6 digit alpha-numeric codes had spaces - so I had to
manually go into my vlookup table and delete all the spaces (yuck) but at
least it worked and only took a few minutes. Once this was fixed it turns
out the alpha-numeric and numeric codes work just fine in the vlookup - the
numerics sort first and then the alpha-numerics, and the forumula has no
trouble finding either and bringing back the right data. So now I'm not sure
what the big deal is about mixed data -- but anyway, thanks for your help!!
(You're right though, I think I do have control over the .csv comma delimited
extracts in how the data comes in - I just never remember till later when I'm
having the problem! Not sure about the vanilla extracts - I'll have to
notice next time.) VG

"arno" wrote:

Hi again VG,

some are pure numbers and others are alpha-numeric, for

no, they are all alpha-numeric, just excel treats them as it likes.


I have no control over what format
these come into Excel as.

maybe you have - in the exportfunction of your database or in the
import to excel.

All of the numerics sort separately from
the alpha-numerics, so my vlookup formula won't work.

this is the problem. there are workarounds for this - in the vlookup
formula like KL suggested or you correct the data eg. with a formula.
Eg. if you have your alphanumeric data in column A you could use this
formula in column B (and copy down to the end): ="'"&A1 this will make
'123 (which is a text, the '-character is invisible but defines the
content as text) out of 123 (which is a number). Then you could copy
column B and PasteSpecial/VALUES to column A - this will overwrite your
mixed numbers/text with only texts from col B. You could have macros
doing this for you.

THEN
RETYPE THE VALUE IN THE CELL

no way

arno

Hi,

now that you know that your vanilla fills up fields with blanks and
that the size is 6 characters you know what to do in your vlookup: fill
up your match kriteria (in a1) with blanks like:

=vlookup(left(a1&" ", 6), table, column, false)

(there's a repeat function that could repeat a " " 6 times, I do not
recall the name in english right now...)

you do not need to remove the blanks from your csv-file, so don't do
it, leave your data what it is.

arno

ps. knowing the field descriptions of tables make the difference

Thanks! I'll try it next time. VG

"arno" wrote:

Hi,

now that you know that your vanilla fills up fields with blanks and
that the size is 6 characters you know what to do in your vlookup: fill
up your match kriteria (in a1) with blanks like:

=vlookup(left(a1&" ", 6), table, column, false)

(there's a repeat function that could repeat a " " 6 times, I do not
recall the name in english right now...)

you do not need to remove the blanks from your csv-file, so don't do
it, leave your data what it is.

arno

ps. knowing the field descriptions of tables make the difference