I hope you saw my other post (about using TRIM) before you started in on
this again.

Rick


"T. Valko" wrote in message
...
your formula will leave the blank spaces in the cell
LT 50835, DLT 6035, or 8227P.

Dang, I didn't even see those spaces but sure enough, they're there! I
think I've strained my eyes enough for one day. I'll try again tomorrow.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
As written, your formula will leave the blank spaces in the cell. That
means entries like these...

AB 1234 and 1234 ABC

will retain the trailing blank space in the first example and the leading
blank space in the second example. The fix is easy.. just add the space
character in with the numbers...

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

or take it out first, before you remove the digits...

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

Both produce the same result for the code samples the OP posted. There is
only one caveat (the same one) with both of these formulas. If the cell
contains an embedded number between two spaces and other characters,
those other characters will all be joined together. So, if you had
this...

ABC 12345 DEF

the final result would be ABCDEF.

Rick


"T. Valko" wrote in message
...
As long as the entries contain just one set of numbers as is shown in
your samples:

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

If the cell contains just a number the formula returns a blank.

100 = blank
xx100 = xx
100xx = xx
xx = xx
10xx25 = incorrect result


--
Biff
Microsoft Excel MVP


"Pat" wrote in message
...
I have a column that has data where most cells in the column contain
only a
number, but some cells contain a one to three letter designation for
example
LT 50835, DLT 6035, or 8227P. I am trying to figure our a formula that
would
let me extract just the letter designation into a new column. I assume
it is
a and lookup with an IF statement but I can't get it figured our. The
ones
without a number would be assigned the negative and I know it would be
If x=x
then a, or x=y then b, or x=z then c, else m. But I just can't figure
out
how to make it look for just the letters within the lookup cell for
each row.