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. |
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. |
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 |
All times are GMT +1. The time now is 10:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com