manpuliation of text and numbers

[jwalkerack]

Hi there Guys ,

I have a 2 phase issue. The first part of it is that i have a text string and it contains a mix of numbers and letters

Sometimes its only numbers

Sometimes its numbers and Letters

Such as 01Z , 10 and 10z .

What i like to be able to do is clear out all of the letters, when they appear. I also want to retain the 01 format for 1. I know if i convert this to numbers and such it might lose this . Anyone have any ideas of how to remove the letters

Regards

 

[Peter_SSs]

Hi Peter , that is great , both of those , examples worked thank you very much

Glad it helped - thanks for letting us know.

 

[István Hirsch]

Just to make the choice wider:

=LEFT(A1,2+1*((MID(A1,3,1))<"A"))

 

[Peter_SSs]

Just to make the choice wider:

=LEFT(A1,2+1*((MID(A1,3,1))<"A"))

Good one István: shorter & fewer functions.

Could we take that process (shorter) even a little further?

=LEFT(A1,2+(MID(A1,3,1)<"A"))

 

[István Hirsch]

Good one István: shorter & fewer functions.

Could we take that process (shorter) even a little further?

=LEFT(A1,2+(MID(A1,3,1)<"A"))

Thanks, Peter. This is even better.

 

[jwalkerack]

Hi guys , i actually noticed i have a problem with this . i probably wrote down wrong , what the contraints where.

This works perfectly if you have a TEXT STRING WHICH is three characters long. such as 19a , it would covnert to 19 perfectly . But it the orginal string was 9a , it would convert to only 9a. is there a way of doing this , when there is differnt counts of strings ?

Cheers Jack

 

[Peter_SSs]

Hi guys , i actually noticed i have a problem with this . i probably wrote down wrong , what the contraints where.

This works perfectly if you have a TEXT STRING WHICH is three characters long. such as 19a , it would covnert to 19 perfectly . But it the orginal string was 9a , it would convert to only 9a.

Yes, you wrote the following which implied at least 2 digits.

The numbers can count up to anything bewteen 01 and 999.

is there a way of doing this

Tried post #7?

 

[István Hirsch]

And is there only one letter at the end?