Cell Formatting

[Andrew Mackenzie]

I have two spreadsheets with lists of account numbers. In one spreadsheet
the list is the lookup vlaue for a Vlookup, while the other is the first
column of the array table for the Vlookup.

The problem is that in the first spreadsheet the account numbers appear to
be formatted as values while in the second they are formatted as text.

The lookup works if I place a sinlge apostrophe in front of the account
number but I don't want to do this individually for everyone - there are
several hundred.


Any suggestions? I dont care how each list is formatted as long as they are
identical so that the lookup works.

I hope this all makes sense.

Regards,

Andrew

 

[Earl Kiosterud]

Andrew,

It sounds as though your search arguments (account numbers) of the VLOOKUP
in the first sheet are the ones you are having to precede with an
apostrophe. And I suspect that the account numbers in the first sheet were
originally numbers, and the column format was then changed to text.
Pressing F2 followed by Enter repeatedly will work your way down, and should
change them to text.

Or for a wholesale way, put in a helper column. If the original account
number is in A2, use:

=A2 & ""

Copy this down the helper column (fill handle, or copy/paste). Now select
it, Copy. Select the original account numbers column, Paste special -
Values. Now you can delete the helper column. Your selections for the copy
and for the paste must be identical, or you'll end up shifting the account
numbers up or down.

 

[Gord Dibben]

Andrew

To change the Text numbers to real numbers.....

Copy an empty cell formatted to General.

Select the range of text numbers and format to General then Paste Special(in
place)>Add>OK>Esc.

Gord Dibben Excel MVP