Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Convert Alpha to Numeric
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. |
#2
|
|||
|
|||
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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Vicki" wrote in message ... 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. |
#3
|
|||
|
|||
On Sun, 20 Mar 2005 21:45:02 -0800, "Vicki"
wrote: 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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert digits to alpha text? | Excel Discussion (Misc queries) | |||
convert a number in Excel from numeric to text, i.e. "1" to "one" | Excel Worksheet Functions | |||
convert cells containing alpha to numeric | Excel Worksheet Functions | |||
Convert Numeric into Text | Excel Worksheet Functions | |||
convert numeric to text | Excel Worksheet Functions |