make Excel see a (one digit) number as text

[Laurence Smith]

This should be so easy but I can't figure out how to do it.

In rating players for a Fantasy Draft I have a small lookup table.

When I rate a player a "2-" for instance I want the numerical
equivalent in my rating system to be 4.

The lookup table works fine for players rated with a minus or a plus,
like 2- or 3+, but I get the dreaded NA when I have rated a guy 1, 2,
3, 4

I assume that Excel sees that as a number, not as text. Is there a way
to get it to see it as text? Just doing a format of the column doesn't
get it done.

Thanks.

 

[GS]

Laurence Smith laid this down on his screen :

This should be so easy but I can't figure out how to do it.

In rating players for a Fantasy Draft I have a small lookup table.

When I rate a player a "2-" for instance I want the numerical
equivalent in my rating system to be 4.

The lookup table works fine for players rated with a minus or a plus,
like 2- or 3+, but I get the dreaded NA when I have rated a guy 1,
2, 3, 4

I assume that Excel sees that as a number, not as text. Is there a
way to get it to see it as text? Just doing a format of the column
doesn't get it done.

Thanks.

Prefix the numeric value with an apostrophe so Excel treats it as text.
(The apostrophe will only display in the Formulabar, not in the cell)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[John Jones]

This should be so easy but I can't figure out how to do it.

In rating players for a Fantasy Draft I have a small lookup table.

When I rate a player a "2-" for instance I want the numerical
equivalent in my rating system to be 4.

The lookup table works fine for players rated with a minus or a plus,
like 2- or 3+, but I get the dreaded NA when I have rated a guy 1,
2, 3, 4

I assume that Excel sees that as a number, not as text. Is there a
way to get it to see it as text? Just doing a format of the column
doesn't get it done.

Thanks.

Prefix the numeric value with an apostrophe so Excel treats it as text.
(The apostrophe will only display in the Formulabar, not in the cell)[/QUOTE]

I couldnt reproduce the problem on Excel 2010...
until I entered the lookup table with apostrophies but kept the item
looked up as a number.

You can force a number to be viewed as a string by concatenating "", eg
vlookup(A3&"",table,column,false)
HTH
JJ

 

[GS]

John Jones used his keyboard to write :

Prefix the numeric value with an apostrophe so Excel treats it as
text. (The apostrophe will only display in the Formulabar, not in
the cell)

I couldnt reproduce the problem on Excel 2010...
until I entered the lookup table with apostrophies but kept the item
looked up as a number.

You can force a number to be viewed as a string by concatenating "",
eg vlookup(A3&"",table,column,false)
HTH
JJ[/QUOTE]

I commonly use numeric indexes for lookup tables and I have never had
an issue. It shouldn't matter what format the lookup criteria is if
both use General, but it still shouldn't matter if either is different.
LOOKUP uses the Value!

I suspect the real problem lies elsewhere!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[CellShocked]

John Jones used his keyboard to write :

I commonly use numeric indexes for lookup tables and I have never had
an issue. It shouldn't matter what format the lookup criteria is if
both use General, but it still shouldn't matter if either is different.
LOOKUP uses the Value!

I suspect the real problem lies elsewhere!

I have used replacement tables as well. A table where a lookup index is
derived, but the value taken and brought back into a named range gets
modified by that chosen value.

Would it not be better to use -1 and -2 as opposed to "1-" and "2-"?

 

[GS]

CellShocked formulated the question :

Would it not be better to use -1 and -2 as opposed to "1-" and "2-"?

I don't see why it can't be either! I guess it's a matter of personal
preference. In the case of a ratings column as in this OP's context, I
don't see why that column can't be formatted as 'Text' to begin with.

<FWIW>
I see tables of data as data tables! That precludes they have fields
and records. Fields contained 'types' of data and so having a 'Text'
field for recording/entering/storing ratings (ie: 1, 1-, 1+,...) just
makes sense when you think about things in terms of data tables.

That said, whether the data is stored in a database (table), a
spreadsheet (table), or a text file (table), the DB tools available to
us all follow the same basic database rules. It just makes sense to me
that if we follow those same rules when working with tables of data
then our task --should-- run problem-free!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion