Removing 2-3 characters at the end
 

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/micro...14ea43bc0279d4

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

Removing 2-3 characters at the end
 

On Jun 8, 3:43*pm, Ty wrote:
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/micro...orksheet.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

Removing 2-3 characters at the end
 

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(INDIREC T("1:99"))))

Rick Rothstein (MVP - Excel)

Removing 2-3 characters at the end
 

On Jun 8, 4:28*pm, "Rick Rothstein"
wrote:
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(INDIRE CT("1:99"))))

Rick Rothstein (MVP - Excel)

Thank you! It works.

Removing 2-3 characters at the end
 

On Wed, 8 Jun 2011 14:33:54 -0700 (PDT), Ty wrote:

On Jun 8, 4:28*pm, "Rick Rothstein"
wrote:
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(INDIRE CT("1:99"))))

Rick Rothstein (MVP - Excel)

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?

Removing 2-3 characters at the end
 

On Jun 8, 7:32*pm, Ron Rosenfeld wrote:
On Wed, 8 Jun 2011 14:33:54 -0700 (PDT), Ty wrote:
On Jun 8, 4:28*pm, "Rick Rothstein"
wrote:
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(INDIR ECT("1:99"))))

Rick Rothstein (MVP - Excel)

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?

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.

Removing 2-3 characters at the end
 

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.

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(INDIREC T("1:99")))),3)

Rick Rothstein (MVP - Excel)

Removing 2-3 characters at the end
 

On Fri, 10 Jun 2011 15:43:34 -0700 (PDT), Ty wrote:

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.