ms excel query

[xirokx]

Hi Guys,

If I had the following:-

cell a1 a2
15d 15


And I want to enter a formula to seperate the d from the 15 so in cell a2 there is 15 only how can I do this? what formula could I use?

Please advise..

Cheers

 

[ricky1981]

will you only ever want to remove the first character or could it possibly be more than one letter then some numbers?

 

[dazzerd]

if there is always going to be 2 didgets before the letter in cell a1 you can use =left(a1,2) in cell a2.

It gets more comlicated if you start to have different lengths of number and different lengths of letters.......

 

[ricky1981]

alternative is to use =left(a1,len(a1)-1)
this counts the number of characters and will work as long you only have one letter after the number (which can be as long as you want)

 

[ricky1981]

scratch that, this should always work

=LEFT(A1,LEN(LOOKUP(9.9E+307,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1))))+0)))