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

 

[XOR LX]

Hi.

Are the numbers always consecutive within the string? Are they always at the very start of the string?

Can you give a few more examples?

Regards

 

[jwalkerack]

In this case , the numbers will always been at the start. But i also need to retain the 01 , if it is a number less than 9 . So at the moment its a text , having 09 is OK , but if its converted to a number it will become 9. The numbers can count up to anything bewteen 01 and 999. The letters if there are any will come after the numbers

 

[XOR LX]

Try this array formula**

=LEFT(A1,MATCH(1,0+ISERR(0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1)

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[jwalkerack]

10	#N/A
11	#N/A
12a	#N/A

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

06b

#N/A

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

Hi there , thank you for that . it did not seem to work it gave me #NA values

 

[XOR LX]

Apologies. Forgot you said that it could be just a number on its own.

Try:

=LEFT(A1,MATCH(1,0+ISERR(0+MID(A1&"ζ",ROW(INDIRECT("1:"&1+LEN(A1))),1)),0)-1)

It also looks as if you didn't follow my instructions re array formulas?

Regards

 

[Peter_SSs]

Would either of these standard entry formulas do what you want?

a) If using Excel 2010 or later:
=LEFT(A1,AGGREGATE(14,6,COUNT(MID(A1,{1,2,3},1)+0),1))

b) For any version:
=LEFT(A1,IF(ISNUMBER(MID(A1,3,1)+0),3,IF(ISNUMBER(MID(A1,2,1)+0),2,1)))

 

[Peter_SSs]

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

Actually, if that is so, this may suffice

=LEFT(A1,IF(ISNUMBER(MID(A1,3,1)+0),3,2))

Edit: .. or

=LEFT(A1,2+ISNUMBER(MID(A1,3,1)+0))

 

[XOR LX]

Actually, if that is so, this may suffice

=LEFT(A1,IF(ISNUMBER(MID(A1,3,1)+0),3,2))

Edit: .. or

=LEFT(A1,2+ISNUMBER(MID(A1,3,1)+0))

Good point. At least someone's thinking!

 

[jwalkerack]

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