I want to compare 2 lists of data. The first list contains data similar to:
D001
D002
R013
R115
L050

The second list:
D1
D2
R13
R115
L50

When I've run vlookup etc, Excel seems to consider the data as different due
to preceding zeros. Is there some way to delete the preceding zeros from the
data in the first list (without deleting trailing zeros)?
Or is it possible to convert both data sets to number format and compare??

I've added this question to the programming section as I need this in VB -
the only part the user will see is dialog box interface.

If there is always one and only one non-numeric character at the beginning,
use this formula to convert your second list (assume the existing entry is in
B1, this formula to get a number you can compare directly to the first list:
=Left(B1,1)&Text(Value(Right(B1,Len(B1)-1)),"000")

"Shane" wrote:

I want to compare 2 lists of data. The first list contains data similar to:
D001
D002
R013
R115
L050

The second list:
D1
D2
R13
R115
L50

When I've run vlookup etc, Excel seems to consider the data as different due
to preceding zeros. Is there some way to delete the preceding zeros from the
data in the first list (without deleting trailing zeros)?
Or is it possible to convert both data sets to number format and compare??

I've added this question to the programming section as I need this in VB -
the only part the user will see is dialog box interface.