would it be difficult to modify your formulas to convert the number to text?
For example if

A1 = 04152365221 (without the single quote)

you could refer to it by TEXT(A1,"00000000000") instead of just A1.


As a generic example, VLOOKUP(A1, Arg2, Arg3, Arg4) would be
VLOOKUP(TEXT(A1,"00000000000"), Arg2, Arg3, Arg4).




"Martina" wrote:

Could someone help me with a data format issue. I am importing student
numbers from a university database which come into my sheet as:

'04152365221

This will not speak to older data (from last month!) which is of the format:

04152365221

I have tried:
Removing the inverted comma.
Doing the multiply by 1
Changing both to General format
Have used the Text to Columns feature.

I can't seem to achieve a common ground. It only works if I physically
retype each number in again. This is really not a viable option.

Any help would be greatly appreciated

regards
Martina