Removing 2-3 characters at the end

  • Thread starter Thread starter Ty
  • Start date Start date
T

Ty

I need to remove 1 or 2 digits at the end of my cell. I did a search
and found the following post from 2003. Don't quite understand all of
it.

http://groups.google.com/group/micr...n&lnk=gst&q=removing+letters#1714ea43bc0279d4

Here is my example related to my previous post which removed the
decimals but some of the cells have letters at the end and some are
too long.

There are 2 different spreadsheets:

1) is the previous spreadsheet in other post using the =SUBSTITUTE. I
can remove the decimals. Is there a way to do the =SUBSTITUTE and
remove the last 2 or 3 characters at the same time?

7.2.9.3.a.b
need to have 729

2) the other spreadsheet. I just need to remove the last 2 or 3 on
the end.
already has 729ab
sometimes 729abc
need to have just 729

Thanks,
T
 
I need to remove 1 or 2 digits at the end of my cell.  I did a search
and found the following post from 2003.  Don't quite understand all of
it.

http://groups.google.com/group/microsoft.public.excel.worksheet.funct...

Here is my example related to my previous post which removed the
decimals but some of the cells have letters at the end and some are
too long.

There are 2 different spreadsheets:

1) is the previous spreadsheet in other post using the =SUBSTITUTE.  I
can remove the decimals.  Is there a way to do the =SUBSTITUTE and
remove the last 2 or 3 characters at the same time?

7.2.9.3.a.b
need to have 729

2) the other spreadsheet.  I just need to remove the last 2 or 3 on
the end.
already has 729ab
sometimes 729abc
need to have just 729

Thanks,
T
Click to expand...

Adding more info. On spreadsheet #2. There are actually some letters
in front of the numbers.

acct729ab
need to have just 729
 
Give this formula a try...

=LOOKUP(9E+99,--LEFT(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1)),".",""),ROW(INDIRECT("1:99"))))

Rick Rothstein (MVP - Excel)
 
Give this formula a try...

=LOOKUP(9E+99,--LEFT(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0­123456789")),LEN(A1)),".",""),ROW(INDIRECT("1:99"))))

Rick Rothstein (MVP - Excel)
Click to expand...

Thank you! It works.
 
One of your examples:

-----------------------------
7.2.9.3.a.b
need to have 729
---------------------------

Rick's formula returns all the digits, so it would return 7293.

Is your example incorrect with a typo?  Or do you really want only the first three digits?
Click to expand...

Yes. I only need the first 3. My way is complicated and is using a
bunch of MID's and several columns and CONCATENATION's.
 
Rick's formula returns all the digits, so it would return 7293.
Yes. I only need the first 3. My way is complicated and is using a
bunch of MID's and several columns and CONCATENATION's.
Click to expand...

In that case, I guess you want this modification to the formula I posted
earlier...

=LEFT(LOOKUP(9E+99,--LEFT(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1)),".",""),ROW(INDIRECT("1:99")))),3)

Rick Rothstein (MVP - Excel)
 
Back
Top