How to normalize data

**excellicious** · 02-05-2009, 10:29 PM

Hello,

An account number should consist of 7 digits. Sheet 1-When the data was downloaded from an application any account that contains a zero in front of the number then it would drop the zero and becomes an 6 digits account (note: not all accounts have a zero in front). How can I normalize this so that when I try to reference/lookup to the correct account number it would know to match.

TIA for your efforts.

**martindwilson** · 02-05-2009, 10:42 PM

it doesnt matter surely, you could format cell as custom 0000000 so cell shows 0123456
but the underlying number would stil be 123456
but a look up or match even if you entered it as 0123456 would still search for 123456 so it would find it.
however if you have formated the numbers say in column E as text so 0123456 shows when typed and your real numbers are in col a and you want Match E1 in col A and return from col B
use
=VLOOKUP(--E1,A:B,2,FALSE) wher the --E converts the text number to a real number
or even sey
IF(--E1=A1,"HELLO,"GO AWAY")

**excellicious** · 02-05-2009, 11:04 PM

I tested with =IF(--Sheet1!A2=Sheet2!B3,"Great","Oh No") and I got the "Oh No" as the result. If I placed the cursor on cell A2, I get 123456. If I placed the cursor on cell B3, I get 0123456..

Thank you

**martindwilson** · 02-05-2009, 11:20 PM

a text number in say cell a1 of 0123456 would if you put in b1 =a1 return exactly that0123456 (orientated to left of cell)
but put in b2 =--a1 returns 0123456 (orientated to right of cell) a test of =isnumber(b1) would show false but of b2 would show true. the -- simply forces the text number to a real number, other ways are to add 0 =a1+0 or multiply by 1 =a1*1
so the answer is yes it will work in index match if you are comparing text numbers to real.