Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 116
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default 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))," ",".")
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 116
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to create a series of numbers passion_queen Excel Discussion (Misc queries) 6 January 30th 09 09:49 PM
Can't sum a series of numbers Cathy Excel Discussion (Misc queries) 10 July 25th 08 02:49 PM
truncate numbers from millions to thousands afreije74 Excel Worksheet Functions 3 September 16th 06 12:09 AM
Series of numbers PA New Users to Excel 4 February 26th 06 01:20 PM
How to change a series of positive numbers to negative numbers Ellie Excel Worksheet Functions 5 September 5th 05 05:10 PM


All times are GMT +1. The time now is 06:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"