replace formula

A

Andy the yeti

Hi, thanks in advance for any help...

I have a series of 8 number codes (eg 12771497) that starts with one of the
numbers from 1 to 26, I would lile to replace for example 1 with A, 2 with B,
3 with C, 26 with Z etc

so it would change from 12771497 > L771497

I tried writting a if =, but after 10 statements it failed to work, any
idea's ??

Thanks again
 
N

Niek Otten

Hi Andy,

If it's always 8 digits, how do we now whether to take 1 or 2 start digits?

iow, why is your example not A2771497?

For your example:

=CHAR(LEFT(A1,2)+64)&RIGHT(A1,6)
 
J

Jay

I have a series of 8 number codes (eg 12771497) that starts with one
of the numbers from 1 to 26, I would lile to replace for example 1
with A, 2 with B, 3 with C, 26 with Z etc

so it would change from 12771497 > L771497
Click to expand...

One way:
=CHAR(CODE("A")+INT(A1/1000000)-1)&MOD(A1,1000000)
 
Top