How to Remove Leading Letters/Characters

[meppwc]

I am trying to find a way of removing any leading characters and letters from cells and leave only numbers
The amount of characters and letters preceding the numbers can vary.

Examples:

AW12345
A12345
AW#12345
etc, etc, etc

 

[Smitty]

See:

Remove Alpha Special characters from Range using Excel VBA

HTH,

 

[Rick Rothstein]

I am trying to find a way of removing any leading characters and letters from cells and leave only numbers
The amount of characters and letters preceding the numbers can vary.

Examples:

AW12345
A12345
AW#12345
etc, etc, etc

Are you looking for a formula that looks at a cell and displays the number from it? If so, try this one..

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)

 

[meppwc]

Thank you Rick........that formula works great, much appreciated

 

[rossa]

Are you looking for a formula that looks at a cell and displays the number from it? If so, try this one..

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)

Cool formula. Would you mind explaining why if I leave out the &"0123456789" piece that an error is given? My understanding of it is that you add all the numbers to cover the bases, but in the case of AW12345 then the 1 should be recognised.

 

[Rick Rothstein]

Cool formula. Would you mind explaining why if I leave out the &"0123456789" piece that an error is given? My understanding of it is that you add all the numbers to cover the bases, but in the case of AW12345 then the 1 should be recognised.

FIND errors out if it cannot find what it is searching for. Find iterates the {0,1,2,3,4,5,6,7,8,9} constant array and if it cannot find a the number in the array it is currently looking for, it would error out, but since we concatenated "0123456789" onto the A1 reference, it is guaranteed to find each digit it searches for. Since FIND returns the position of the text it is searching for, the MIN function guarantees the location of the first digit in A1 is returned to the MID function. Note that if no digits exist in the text, the empty string ("") is returned from the MID function because the found digit in that case will be one of the concatenated digits and the position of each of those is past the end of the text.

 

[rossa]

Thanks for the quick response Rick. I have the concept now, I just wanted to understand the logic.