separating numbers and letters from alphanumeric cell contents

Harlan,

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

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

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

On Sat, 3 Sep 2005 03:02:03 -0500, Krishnakumar
<[email protected]> 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

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

On 30 Aug 2005 09:57:57 -0700, "PH" <[email protected]> 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

That's it! you guys are freaking awesome.

Thanks!

PH

Dave,

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

PH

On 30 Aug 2005 09:57:57 -0700, "PH" <[email protected]> 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

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

Dave,

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

PH

That's it! you guys are freaking awesome.

Thanks!

PH

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

On Sat, 3 Sep 2005 03:02:03 -0500, Krishnakumar
<[email protected]> 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

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

That's it! you guys are freaking awesome.

Thanks!

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

On 30 Aug 2005 09:57:57 -0700, "PH" <[email protected]> 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

On Sat, 3 Sep 2005 03:02:03 -0500, Krishnakumar
<[email protected]> 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

Harlan,

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

PH

Dave,

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

PH

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

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

Harlan,

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

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

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 Sat, 3 Sep 2005 03:02:03 -0500, Krishnakumar
<[email protected]> 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

Dave,

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

PH

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

On 30 Aug 2005 09:57:57 -0700, "PH" <[email protected]> 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

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

On Sat, 3 Sep 2005 03:02:03 -0500, Krishnakumar
<[email protected]> 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

On 30 Aug 2005 09:57:57 -0700, "PH" <[email protected]> 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

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

That's it! you guys are freaking awesome.

Thanks!

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

Harlan,

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

PH

On 30 Aug 2005 09:57:57 -0700, "PH" <[email protected]> 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

On Sat, 3 Sep 2005 03:02:03 -0500, Krishnakumar
<[email protected]> 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