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.

Think you could try the UDF below, which is modified slightly from a googled
thread by Gary''s Student & Rick Rothstein

To install the UDF:
Copy the UDF (copy all that's within the dotted lines), then press Alt+F11
to go to VBE. Click Insert Module, then paste the UDF in the code window
(the blank white space on the right). Press Alt+Q to get back to Excel.

In Excel, in any sheet,
with source data in A1 down
you could place in B1: =alphas(A1)
and copy down to extract only the alphas from the source data

'--- begin --
Public Function Alphas(rng As Range) As String
Dim sStr As String, i As Long, sStr1 As String
Dim sChar As String
sStr = rng.Value
For i = 1 To Len(sStr)
sChar = Mid(sStr, i, 1)
If sChar Like "[a-zA-Z]" Then
sStr1 = sStr1 & sChar
End If
Next
Alphas = sStr1
End Function
'--- end --

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"Pat" wrote:
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.

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.

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.

Doh! Or you could handle the space character problem in your original
formula correctly by simply slapping the TRIM function around it...

=TRIM(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},"")))),"") )

Rick


"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.

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.

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.