general number format and VLOOKUP

[Guest]

our report writer returns numbers in general format however initially each
cell has an Excel "error" note (green triangle in top left corner) to say the
numbers in the cell are formatted as text or preceded by an apostrophe.
VLOOKUP won't work until this "error" is cleared. I can clear it by pressing
F2 and Enter within each cell and then VLOOKUP works fine but is there a
quicker or automated way to do this as it gets to be a pain when you have
2000 rows of data.

thanks

 

[Guest]

When you get the message there's an exclamation mark that pops up - with all
your values selected press the exclamation mark to get a pop-up menu and
select convert to number.

This will convert all the values to number format in one hit.

A quicker way, but not the ideal solution - which would be to have your
report writer place them as the same format as the VLOOKUP in the first place.

 

[Guest]

thanks Darren - simple solution but works brilliantly. I can't tell you how
much time I have previously wasted with the F2+Enter approach - duh!

10 out of 10!

 

[Guest]

Glad to help

 

[PianistFromJersey]

I have a similar problem even when I have formatted all the values in a
column with the same format - either number or text. The VLOOKUP and MATCH
functions sometimes do not work unless, like Pauline Warner below, I press
F2/Enter in each cell, or click on the edit bar at the top of the screen,
then Enter (They give #N/a eerror). It is as if the spreadsheet does not know
what the format is until you hit Enter.

When you get the message there's an exclamation mark that pops up - with all
your values selected press the exclamation mark to get a pop-up menu and
select convert to number.

This will convert all the values to number format in one hit.

A quicker way, but not the ideal solution - which would be to have your
report writer place them as the same format as the VLOOKUP in the first place.

Question

our report writer returns numbers in general format however initially each
cell has an Excel "error" note (green triangle in top left corner) to say the
numbers in the cell are formatted as text or preceded by an apostrophe.
VLOOKUP won't work until this "error" is cleared. I can clear it by pressing
F2 and Enter within each cell and then VLOOKUP works fine but is there a
quicker or automated way to do this as it gets to be a pain when you have
2000 rows of data.

thanks

 

[Pete_UK]

Darren gave Pauline a tip on how to change them all in one go - have
you tried that?

Alternatively, if your lookup table contains text values that look
like numbers and your lookup value is a proper number, then you could
modify your VLOOKUP formula like this:

=VLOOKUP(A1&"",lookup_table,2,0)

where A1 contains your lookup value, and the formula thus converts the
proper number to text.

Hope this helps.

Pete

 

[Patty]

I have a file where the #'s are formatted as text and the VLOOKUP will not
work. I can go to format/cells and change to number, but when I click "ok",
nothing has changed. If I "copy/paste special values" the cells to another
column, it still comes over as text. However, if I type the # in another
column, the vlookup will work on this column. What do I need to do to change
the column of #'s so the VLOOKUP will work without retyping all of the
entries? Thanks!

 

[Peo Sjoblom]

Format an empty cell as number, copy the same cell, select the text numbers
and do edit>paste special
and select add


--


Regards,


Peo Sjoblom