Convert Alpha to Numeric

V

Vicki

I have two spreadsheet which I would like to compare. One has numeric data,
the other has the same data but the references codes have been entered as
alpha. Does anyone know how this can be converted to numeric?

Many thanks for your help.
 
M

Max

Not sure, but maybe as a start, you could try something along these lines
using VLOOKUP and CODE() ?

Assume the sample (numeric) data below is
in Sheet1 cols A and B,
with the numeric refcodes in col A

RefCode Field1
65 Text1
66 Text2
67 Text3
etc

And you have in Sheet2,
the alphas as the RefCodes in col A

RefCode Field1
A Text1
B Text2
C Text3
etc

Put 2 labels into C1:D1 : Compare, Result

Put in C2: =VLOOKUP(CODE(TRIM(A2)),Sheet1!A:B,2,0)
Put in D2: =TRIM(B2)=TRIM(C2)

Select C2:D2 and fill down

Col C ("Compare") extracts over the matched items for the RefCodes (A,B,C)
from the col Field1 in Sheet1 for comparison, while col D (Result") gives
the result of the comparison of col C with what you have in col B in Sheet2
(TRUE = yes, matched ; FALSE = no, does not match)

For the sample data above, what you'll get is:

RefCode Field1 Compare Result
A Text1 Text1 TRUE
B Text2 Text2 TRUE
C Text3 Text3 TRUE
etc
 
R

Ron Rosenfeld

I have two spreadsheet which I would like to compare. One has numeric data,
the other has the same data but the references codes have been entered as
alpha. Does anyone know how this can be converted to numeric?

Many thanks for your help.
Click to expand...

If I understand you correctly:

Information that looks like a number but is really TEXT can be changed to a
number by performing some kind of arithmetic operation.

So, for a comparison, if the numeric data is in A1 and the TEXT data is in B1,
and both of them are 12345; the formula:

=A1=B1 :FALSE
=A1=--B1 :TRUE

The double unary preceding B1 forces Excel to convert it to a number.


--ron
 
Top