separating numbers and letters from alphanumeric cell contents
 

I know the subject line is a bit vague, let me explain.

Using Excel 2000, I have a cell "A1" with the value "18E" in it. In
cell "A2" I need to report *only* the number value in cell "A1", and in
cell "A3" I need *only* the letter found in cell "A1."

The problem: the contents of "A1" could be any value from 0 to 100 and
there *may or may not* be any letter at all in the cell. I need the
number reported regardless of what it is, and I need the letter to be
reported, but if it's not present I need it to report something like
"none."

Caveat: I can't use any macros at all in this worksheet, so no macro
answers can be used.

Any non-macro assistance you can give me is greatly appreciated.

Thanks!
PH

PH wrote...
....
Using Excel 2000, I have a cell "A1" with the value "18E" in it. In
cell "A2" I need to report *only* the number value in cell "A1", and in
cell "A3" I need *only* the letter found in cell "A1."

The problem: the contents of "A1" could be any value from 0 to 100 and
there *may or may not* be any letter at all in the cell. I need the
number reported regardless of what it is, and I need the letter to be
reported, but if it's not present I need it to report something like
"none."
....

Simple enough to do with formulas alone. If your column A values would
never exceed 4 characters in length (up to 3 decimal numerals and one
letter), use the following to parse the numerals.

A2:
=LOOKUP(1000,-MID(A1,1,{1;2;3}),MID(A1,1,{1;2;3}))

A3:
=IF(LEN(A1)<LEN(A2),RIGHT(A1,1),"none")

Why can't you use macros?

First, let me ask you... if there is a letter in the value, will it always
be last in the string? Also, will there always be just one letter or can
there be multiple?
--
Regards,
Dave


"PH" wrote:

I know the subject line is a bit vague, let me explain.

Using Excel 2000, I have a cell "A1" with the value "18E" in it. In
cell "A2" I need to report *only* the number value in cell "A1", and in
cell "A3" I need *only* the letter found in cell "A1."

The problem: the contents of "A1" could be any value from 0 to 100 and
there *may or may not* be any letter at all in the cell. I need the
number reported regardless of what it is, and I need the letter to be
reported, but if it's not present I need it to report something like
"none."

Caveat: I can't use any macros at all in this worksheet, so no macro
answers can be used.

Any non-macro assistance you can give me is greatly appreciated.

Thanks!
PH

Dave,

The letter will *always* be last in the series. There will be only one
letter.

PH

PH Wrote:
I know the subject line is a bit vague, let me explain.

Using Excel 2000, I have a cell "A1" with the value "18E" in it. In
cell "A2" I need to report *only* the number value in cell "A1", and
in
cell "A3" I need *only* the letter found in cell "A1."

The problem: the contents of "A1" could be any value from 0 to 100 and
there *may or may not* be any letter at all in the cell. I need the
number reported regardless of what it is, and I need the letter to be
reported, but if it's not present I need it to report something like
"none."

Caveat: I can't use any macros at all in this worksheet, so no macro
answers can be used.

Any non-macro assistance you can give me is greatly appreciated.

Thanks!
PH
Try
In A2 =IF(ISNUMBER(A$1),A$1,LEFT(A$1,LEN(A$1)-1))
In A3 =IF(ISNUMBER(A$1),"none",RIGHT(A$1,1))
Ilan


--
ilanr01
------------------------------------------------------------------------
ilanr01's Profile: http://www.excelforum.com/member.php...o&userid=26797
View this thread: http://www.excelforum.com/showthread...hreadid=400521

Harlan,

Your A2 forumula works great, but the A3 formula only ever reponds
"none," and not the letter.

PH

PH wrote...
Your A2 forumula works great, but the A3 formula only ever reponds
"none," and not the letter.

That's because I screwed up my A3 formula. It should be

=IF(LEN(A1)LEN(A2),RIGHT(A1,1),"none")

That's it! you guys are freaking awesome.

Thanks!

PH

On 30 Aug 2005 09:57:57 -0700, "PH" wrote:

I know the subject line is a bit vague, let me explain.

Using Excel 2000, I have a cell "A1" with the value "18E" in it. In
cell "A2" I need to report *only* the number value in cell "A1", and in
cell "A3" I need *only* the letter found in cell "A1."

The problem: the contents of "A1" could be any value from 0 to 100 and
there *may or may not* be any letter at all in the cell. I need the
number reported regardless of what it is, and I need the letter to be
reported, but if it's not present I need it to report something like
"none."

Caveat: I can't use any macros at all in this worksheet, so no macro
answers can be used.

Any non-macro assistance you can give me is greatly appreciated.

Thanks!
PH

Assuming there can only be a maximum of one letter, and it will be at the end,
then this might work:

For the number:

=IF(ISERROR(-RIGHT(A1,1)),LEFT(A1,LEN(A1)-1),A1)

For the letter:

=IF(ISERROR(-RIGHT(A1,1)),RIGHT(A1,1),"none")

Note that the "number" formula returns the number as TEXT. If you need it
returned as a NUMBER, then:

=IF(ISERROR(-RIGHT(A1,1)),--LEFT(A1,LEN(A1)-1),A1)


--ron

Assuming there can only be a maximum of one letter, and it will be at
the end,

Another option..

For Letter,

In B1

=REPLACE(A1,1,LEN(A1)-1,"")

For Number,

=--SUBSTITUTE(A1,B1,"")

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=400521

On Sat, 3 Sep 2005 03:02:03 -0500, Krishnakumar
wrote:


Assuming there can only be a maximum of one letter, and it will be at
the end,

Another option..

For Letter,

In B1

=REPLACE(A1,1,LEN(A1)-1,"")

For Number,

=--SUBSTITUTE(A1,B1,"")

HTH

Doesn't work if there is no letter.


--ron