Help separating letters from numbers

[FJ]

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.

 

[RagDyer]

Your example shows only a *single* alpha at the end of an entry.

Is that *always* the case?

 

[FJ]

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.

 

[AltaEgo]

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/excel/HA011549011033.aspx

http://www.ozgrid.com/VBA/ExtractNum.htm

 

[Lars-Åke Aspelin]

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

 

[RagDyer]

Try this for a single alpha:

=IF(ISERR(--RIGHT(A1)),LEFT(A1,LEN(A1)-1),A1)

 

[Ron Rosenfeld]

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.

I would probably use a UDF but first, a formula that seems to work, given that
there will be only a single string of digits in the number.

=LEFT(A1,MIN(LEN(A1),MIN(SEARCH(CHAR(ROW(INDIRECT("65:90"))),
A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ",MIN(
FIND({0,1,2,3,4,5,6,7,8,9,0},A1&"0123456789")))))-1)

If I understand you correctly, what you want to do could also be expressed as
removing any non-digits that occur at the end of the string.

That being the case, this User Defined Function can also do this.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=StripTerminalNonDigit(A1)

in some cell.

===========================
Option Explicit
Function StripTerminalNonDigit(s As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D*$"
StripTerminalNonDigit = re.Replace(s, "")
End Function
=======================
--ron

 

[FJ]

Hi, thanks for your response. Your formula worked great.

 

[FJ]

Hi, thanks for your response. I tried your formula and it worked great.

 

[FJ]

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]

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,,,LEN(A1))),1))*ROW(OFFSET(A$1,,,LEN(A1)))))

Hope this helps / Lars-Åke

 

[FJ]

Hi, Lars, thanks for your response. I tried your revised formula and it
works great. Thanks again!