Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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") |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
Dave,
The letter will *always* be last in the series. There will be only one letter. PH |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
Harlan,
Your A2 forumula works great, but the A3 formula only ever reponds "none," and not the letter. PH |
#7
|
|||
|
|||
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") |
#8
|
|||
|
|||
That's it! you guys are freaking awesome.
Thanks! PH |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
no row numbers or column letters DISPLAYED | Excel Discussion (Misc queries) | |||
How do I sort letters before numbers in Excel? | Excel Discussion (Misc queries) | |||
Columns in letters vs numbers | Excel Discussion (Misc queries) | |||
VLOOKUP for a cell with both letters and numbers | Excel Discussion (Misc queries) | |||
Excel column headings from numbers to letters | Excel Discussion (Misc queries) |