Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create a series of numbers | Excel Discussion (Misc queries) | |||
Can't sum a series of numbers | Excel Discussion (Misc queries) | |||
truncate numbers from millions to thousands | Excel Worksheet Functions | |||
Series of numbers | New Users to Excel | |||
How to change a series of positive numbers to negative numbers | Excel Worksheet Functions |