Removing 2-3 characters at the end

[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

 

[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/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

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

acct729ab
need to have just 729

 

[Rick Rothstein]

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)

 

[Ty]

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)

Thank you! It works.

 

[Ron Rosenfeld]

Thank you! It works.

One of your examples:

 

[Ty]

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?

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 Rothstein]

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.

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)

 

[Ron Rosenfeld]

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.

I see Rick has already posted a modification to his formula to limit the return to only three digits.

So long as you have covered all the variations in data in and fully specified what you want out, that formula will work just fine.

If your digits might be separated by characters other than periods (.), then you need a different solution.