Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help separating letters from numbers
Hi, I have what is probably a very simple problem but I cant figure out a
quick solution. I have thousands of rows of mixed numbers and letters, such as: ABC000825A DEF0125B AB9037563 DE075782989F EFC2987899 And I have to delete the letters from the end of each entry. The problem is that not every entry has the same number of digits and not all of them end in letters. Is there a quick way to do this? I have tried various things but so far nothing as worked. I know this is probably easy but I cant seem to figure it out. Thanks in advance for any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help separating letters from numbers
Your example shows only a *single* alpha at the end of an entry.
Is that *always* the case? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FJ" wrote in message ... Hi, I have what is probably a very simple problem but I can't figure out a quick solution. I have thousands of rows of mixed numbers and letters, such as: ABC000825A DEF0125B AB9037563 DE075782989F EFC2987899 And I have to delete the letters from the end of each entry. The problem is that not every entry has the same number of digits and not all of them end in letters. Is there a quick way to do this? I have tried various things but so far nothing as worked. I know this is probably easy but I can't seem to figure it out. Thanks in advance for any help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help separating letters from numbers
Just scrolling through quickly that seems to be the case, although it's not
my file so I suppose there might be a few exceptions. It's just such a huge file that it's hard to tell. "RagDyer" wrote: Your example shows only a *single* alpha at the end of an entry. Is that *always* the case? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FJ" wrote in message ... Hi, I have what is probably a very simple problem but I can't figure out a quick solution. I have thousands of rows of mixed numbers and letters, such as: ABC000825A DEF0125B AB9037563 DE075782989F EFC2987899 And I have to delete the letters from the end of each entry. The problem is that not every entry has the same number of digits and not all of them end in letters. Is there a quick way to do this? I have tried various things but so far nothing as worked. I know this is probably easy but I can't seem to figure it out. Thanks in advance for any help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help separating letters from numbers
I presume you mean if there is a single letter on the r/h end, chop it off.
=IF(ISNUMBER(--RIGHT(A1,1)),A1,LEFT(A1,LEN(A1)-1)) If not one of the following may help: http://office.microsoft.com/en-us/ex...549011033.aspx http://www.ozgrid.com/VBA/ExtractNum.htm -- Steve "FJ" wrote in message ... Hi, I have what is probably a very simple problem but I cant figure out a quick solution. I have thousands of rows of mixed numbers and letters, such as: ABC000825A DEF0125B AB9037563 DE075782989F EFC2987899 And I have to delete the letters from the end of each entry. The problem is that not every entry has the same number of digits and not all of them end in letters. Is there a quick way to do this? I have tried various things but so far nothing as worked. I know this is probably easy but I cant seem to figure it out. Thanks in advance for any help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help separating letters from numbers
Try this formula:
It will remove everything to the right of the rightmost digit. (Your examples only show 0 or 1 letters to be removed, but as you could not say for sure that there is never more than 1 letter this formula takes care of that case as well) =LEFT(A1,MAX(ISNUMBER(0+MID(A1,ROW(OFFSET(A1,,,LEN (A1))),1))*ROW(OFFSET(A1,,,LEN(A1))))) Note: This is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke On Tue, 28 Jul 2009 16:30:28 -0700, FJ wrote: Just scrolling through quickly that seems to be the case, although it's not my file so I suppose there might be a few exceptions. It's just such a huge file that it's hard to tell. "RagDyer" wrote: Your example shows only a *single* alpha at the end of an entry. Is that *always* the case? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FJ" wrote in message ... Hi, I have what is probably a very simple problem but I can't figure out a quick solution. I have thousands of rows of mixed numbers and letters, such as: ABC000825A DEF0125B AB9037563 DE075782989F EFC2987899 And I have to delete the letters from the end of each entry. The problem is that not every entry has the same number of digits and not all of them end in letters. Is there a quick way to do this? I have tried various things but so far nothing as worked. I know this is probably easy but I can't seem to figure it out. Thanks in advance for any help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help separating letters from numbers
Try this for a single alpha:
=IF(ISERR(--RIGHT(A1)),LEFT(A1,LEN(A1)-1),A1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FJ" wrote in message ... Just scrolling through quickly that seems to be the case, although it's not my file so I suppose there might be a few exceptions. It's just such a huge file that it's hard to tell. "RagDyer" wrote: Your example shows only a *single* alpha at the end of an entry. Is that *always* the case? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FJ" wrote in message ... Hi, I have what is probably a very simple problem but I can't figure out a quick solution. I have thousands of rows of mixed numbers and letters, such as: ABC000825A DEF0125B AB9037563 DE075782989F EFC2987899 And I have to delete the letters from the end of each entry. The problem is that not every entry has the same number of digits and not all of them end in letters. Is there a quick way to do this? I have tried various things but so far nothing as worked. I know this is probably easy but I can't seem to figure it out. Thanks in advance for any help. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help separating letters from numbers
Hi, thanks for your response. Your formula worked great. :)
"RagDyer" wrote: Try this for a single alpha: =IF(ISERR(--RIGHT(A1)),LEFT(A1,LEN(A1)-1),A1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FJ" wrote in message ... Just scrolling through quickly that seems to be the case, although it's not my file so I suppose there might be a few exceptions. It's just such a huge file that it's hard to tell. "RagDyer" wrote: Your example shows only a *single* alpha at the end of an entry. Is that *always* the case? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FJ" wrote in message ... Hi, I have what is probably a very simple problem but I can't figure out a quick solution. I have thousands of rows of mixed numbers and letters, such as: ABC000825A DEF0125B AB9037563 DE075782989F EFC2987899 And I have to delete the letters from the end of each entry. The problem is that not every entry has the same number of digits and not all of them end in letters. Is there a quick way to do this? I have tried various things but so far nothing as worked. I know this is probably easy but I can't seem to figure it out. Thanks in advance for any help. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help separating letters from numbers
Hi, thanks for your response. I tried your formula and it worked great. :)
"AltaEgo" wrote: I presume you mean if there is a single letter on the r/h end, chop it off. =IF(ISNUMBER(--RIGHT(A1,1)),A1,LEFT(A1,LEN(A1)-1)) If not one of the following may help: http://office.microsoft.com/en-us/ex...549011033.aspx http://www.ozgrid.com/VBA/ExtractNum.htm -- Steve "FJ" wrote in message ... Hi, I have what is probably a very simple problem but I cant figure out a quick solution. I have thousands of rows of mixed numbers and letters, such as: ABC000825A DEF0125B AB9037563 DE075782989F EFC2987899 And I have to delete the letters from the end of each entry. The problem is that not every entry has the same number of digits and not all of them end in letters. Is there a quick way to do this? I have tried various things but so far nothing as worked. I know this is probably easy but I cant seem to figure it out. Thanks in advance for any help. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help separating letters from numbers
Hi, Lars, thanks for your response. Your formula worked great for the first
14 rows of the spreadsheet, but then it just yielded blank cells. Is there a way to modify the formula so it will work all the way down the column? Thanks in advance for any information. "Lars-Ã…ke Aspelin" wrote: Try this formula: It will remove everything to the right of the rightmost digit. (Your examples only show 0 or 1 letters to be removed, but as you could not say for sure that there is never more than 1 letter this formula takes care of that case as well) =LEFT(A1,MAX(ISNUMBER(0+MID(A1,ROW(OFFSET(A1,,,LEN (A1))),1))*ROW(OFFSET(A1,,,LEN(A1))))) Note: This is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Ã…ke On Tue, 28 Jul 2009 16:30:28 -0700, FJ wrote: Just scrolling through quickly that seems to be the case, although it's not my file so I suppose there might be a few exceptions. It's just such a huge file that it's hard to tell. "RagDyer" wrote: Your example shows only a *single* alpha at the end of an entry. Is that *always* the case? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FJ" wrote in message ... Hi, I have what is probably a very simple problem but I can't figure out a quick solution. I have thousands of rows of mixed numbers and letters, such as: ABC000825A DEF0125B AB9037563 DE075782989F EFC2987899 And I have to delete the letters from the end of each entry. The problem is that not every entry has the same number of digits and not all of them end in letters. Is there a quick way to do this? I have tried various things but so far nothing as worked. I know this is probably easy but I can't seem to figure it out. Thanks in advance for any help. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help separating letters from numbers
Ooops, my mistake. There should be $ in two places. Please try this modified formuila: =LEFT(A1,MAX(ISNUMBER(0+MID(A1,ROW(OFFSET(A$1,,,LE N(A1))),1))*ROW(OFFSET(A$1,,,LEN(A1))))) Hope this helps / Lars-Åke On Tue, 28 Jul 2009 20:01:01 -0700, FJ wrote: Hi, Lars, thanks for your response. Your formula worked great for the first 14 rows of the spreadsheet, but then it just yielded blank cells. Is there a way to modify the formula so it will work all the way down the column? Thanks in advance for any information. "Lars-Åke Aspelin" wrote: Try this formula: It will remove everything to the right of the rightmost digit. (Your examples only show 0 or 1 letters to be removed, but as you could not say for sure that there is never more than 1 letter this formula takes care of that case as well) =LEFT(A1,MAX(ISNUMBER(0+MID(A1,ROW(OFFSET(A1,,,LEN (A1))),1))*ROW(OFFSET(A1,,,LEN(A1))))) Note: This is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke On Tue, 28 Jul 2009 16:30:28 -0700, FJ wrote: Just scrolling through quickly that seems to be the case, although it's not my file so I suppose there might be a few exceptions. It's just such a huge file that it's hard to tell. "RagDyer" wrote: Your example shows only a *single* alpha at the end of an entry. Is that *always* the case? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FJ" wrote in message ... Hi, I have what is probably a very simple problem but I can't figure out a quick solution. I have thousands of rows of mixed numbers and letters, such as: ABC000825A DEF0125B AB9037563 DE075782989F EFC2987899 And I have to delete the letters from the end of each entry. The problem is that not every entry has the same number of digits and not all of them end in letters. Is there a quick way to do this? I have tried various things but so far nothing as worked. I know this is probably easy but I can't seem to figure it out. Thanks in advance for any help. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help separating letters from numbers
Hi, Lars, thanks for your response. I tried your revised formula and it
works great. :) Thanks again! "Lars-Ã…ke Aspelin" wrote: Ooops, my mistake. There should be $ in two places. Please try this modified formuila: =LEFT(A1,MAX(ISNUMBER(0+MID(A1,ROW(OFFSET(A$1,,,LE N(A1))),1))*ROW(OFFSET(A$1,,,LEN(A1))))) Hope this helps / Lars-Ã…ke On Tue, 28 Jul 2009 20:01:01 -0700, FJ wrote: Hi, Lars, thanks for your response. Your formula worked great for the first 14 rows of the spreadsheet, but then it just yielded blank cells. Is there a way to modify the formula so it will work all the way down the column? Thanks in advance for any information. "Lars-Ã…ke Aspelin" wrote: Try this formula: It will remove everything to the right of the rightmost digit. (Your examples only show 0 or 1 letters to be removed, but as you could not say for sure that there is never more than 1 letter this formula takes care of that case as well) =LEFT(A1,MAX(ISNUMBER(0+MID(A1,ROW(OFFSET(A1,,,LEN (A1))),1))*ROW(OFFSET(A1,,,LEN(A1))))) Note: This is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Ã…ke On Tue, 28 Jul 2009 16:30:28 -0700, FJ wrote: Just scrolling through quickly that seems to be the case, although it's not my file so I suppose there might be a few exceptions. It's just such a huge file that it's hard to tell. "RagDyer" wrote: Your example shows only a *single* alpha at the end of an entry. Is that *always* the case? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "FJ" wrote in message ... Hi, I have what is probably a very simple problem but I can't figure out a quick solution. I have thousands of rows of mixed numbers and letters, such as: ABC000825A DEF0125B AB9037563 DE075782989F EFC2987899 And I have to delete the letters from the end of each entry. The problem is that not every entry has the same number of digits and not all of them end in letters. Is there a quick way to do this? I have tried various things but so far nothing as worked. I know this is probably easy but I can't seem to figure it out. Thanks in advance for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
separating numbers from text | Excel Worksheet Functions | |||
Separating Text From Numbers | Excel Discussion (Misc queries) | |||
separating +ve and -ve numbers into two columns | Excel Discussion (Misc queries) | |||
separating numbers and letters from alphanumeric cell contents | Excel Worksheet Functions | |||
Separating Numbers | Excel Worksheet Functions |