Truncate a series of numbers

[Carrie_Loos via OfficeKB.com]

Hi -

I am trying to truncate these numbers to only 2 sections; example: from 51230.

14.6.24 to 51230.14 There can be multiple quantities of numbers in each
section; it can be 51230.1434.66.789. I used the function below but it leaves
a "." at the end which I do not want. As in the example I need to have just
51230.14 Any suggestions?

Thanks

=LEFT(K2,FIND(CHAR(7),SUBSTITUTE(K2,".",CHAR(7),2)))

Thanks in advance,
Carrie

 

[Glenn]

Hi -

I am trying to truncate these numbers to only 2 sections; example: from 51230.

14.6.24 to 51230.14 There can be multiple quantities of numbers in each
section; it can be 51230.1434.66.789. I used the function below but it leaves
a "." at the end which I do not want. As in the example I need to have just
51230.14 Any suggestions?

Thanks

=LEFT(K2,FIND(CHAR(7),SUBSTITUTE(K2,".",CHAR(7),2)))

Thanks in advance,
Carrie

Borrowing from an answer I posted earlier today...

=SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(K2,".",REPT(" ",99)),198))," ",".")

 

[Rick Rothstein]

If that formula gives you the correct result except for an "extra" dot at
the end, then just subtract 1 from what the FIND function returns (the
location of the second dot) and you should have your answer...

=LEFT(K2,FIND(CHAR(7),SUBSTITUTE(K2,".",CHAR(7),2))-1)

For future questions you may ask on the newsgroups, it is always a good idea
to show a few to several "before and after" examples of what you want in
addition to trying to describe it in words.

 

[Carrie_Loos via OfficeKB.com]

Thank you - worked great!

Hi -

[quoted text clipped - 11 lines]

Thanks in advance,
Carrie

Borrowing from an answer I posted earlier today...

=SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(K2,".",REPT(" ",99)),198))," ",".")