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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.