separating numbers and letters from alphanumeric cell contents

[PH]

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

 

[Harlan Grove]

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

 

[Guest]

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?

 

[PH]

Dave,

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

PH

 

[ilanr01]

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", an
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))
Ila

 

[PH]

Harlan,

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

PH

 

[Harlan Grove]

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

 

[PH]

That's it! you guys are freaking awesome.

Thanks!

PH

 

[Ron Rosenfeld]

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

 

[Krishnakumar]

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

 

[Ron Rosenfeld]

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