Vlookup referencing one column that contains part numbers in both.

I'm seeing LOTS of posts that are close to this issue, but nothing quite the
same.

I'm using VLOOKUP to look at a part number column that's being imported. It
all comes across in text format b/c some part numbers are alphanumeric (ex:
6H) and some are numeric (ex: 30). Nothing I've seen so far addresses the
issue b/c if I change the entire column to text I get #N/A back for cells in
the column that should show a number in them, (ex:30). If I change the entire
column to numeric format I get the same for cells that contain alphanumeric
data (ex: 6H).

Does anyone know of a solution that might address, seeing how there are two
different types of data formats in the same column being referenced?!!

Try something like this:

For ParNumbers in Col_A and a lookup table in Columns C:D

Set all of the values in the Col_C of the lookup table Text.

Then use a formula like this:
B1: =VLOOKUP(A1&"",B:C,2,0)

Appending an empty string to the numeric P/N's converts them to text.
Appending an empty string to the text P/N's has no impact.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"crafty_girl" wrote:

> I'm seeing LOTS of posts that are close to this issue, but nothing quite the
> same.
>
> I'm using VLOOKUP to look at a part number column that's being imported. It
> all comes across in text format b/c some part numbers are alphanumeric (ex:
> 6H) and some are numeric (ex: 30). Nothing I've seen so far addresses the
> issue b/c if I change the entire column to text I get #N/A back for cells in
> the column that should show a number in them, (ex:30). If I change the entire
> column to numeric format I get the same for cells that contain alphanumeric
> data (ex: 6H).
>
> Does anyone know of a solution that might address, seeing how there are two
> different types of data formats in the same column being referenced?!!

Thanks Ron.

Unless I'm doing something wrong, that's not doing it. It doesn't make
things look any different than it did before. MAN this is frustrating!

"Ron Coderre" wrote:

> Try something like this:
>
> For ParNumbers in Col_A and a lookup table in Columns C:D
>
> Set all of the values in the Col_C of the lookup table Text.
>
> Then use a formula like this:
> B1: =VLOOKUP(A1&"",B:C,2,0)
>
> Appending an empty string to the numeric P/N's converts them to text.
> Appending an empty string to the text P/N's has no impact.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "crafty_girl" wrote:
>
> > I'm seeing LOTS of posts that are close to this issue, but nothing quite the
> > same.
> >
> > I'm using VLOOKUP to look at a part number column that's being imported. It
> > all comes across in text format b/c some part numbers are alphanumeric (ex:
> > 6H) and some are numeric (ex: 30). Nothing I've seen so far addresses the
> > issue b/c if I change the entire column to text I get #N/A back for cells in
> > the column that should show a number in them, (ex:30). If I change the entire
> > column to numeric format I get the same for cells that contain alphanumeric
> > data (ex: 6H).
> >
> > Does anyone know of a solution that might address, seeing how there are two
> > different types of data formats in the same column being referenced?!!