Truncate a series of numbers
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200909/1

Truncate a series of numbers
 

Carrie_Loos via OfficeKB.com wrote:
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))," ",".")

Truncate a series of numbers
 

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.

--
Rick (MVP - Excel)


"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:9be038d874039@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200909/1

Truncate a series of numbers
 

Thank you - worked great!

Glenn wrote:
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))," ",".")

--
Message posted via http://www.officekb.com