separating numbers and letters from alphanumeric cell contents

On 30 Aug 2005 09:57:57 -0700, "PH" <philh69@hotmail.com> 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

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

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

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

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" <philh69@hotmail.com> 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

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

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

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

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

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" <philh69@hotmail.com> 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

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

On 30 Aug 2005 09:57:57 -0700, "PH" <philh69@hotmail.com> 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

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

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

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

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" <philh69@hotmail.com> 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

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