 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008How to truncate digits?
Using Excel 97 7018 kbps (857 kB/s) 13043 kbps (1592 kB/s) Above is an example of lines of data I get generated from a speed test. How do I truncate those last parts, the parts beyond the ( digit? I'm needing 7018 kbps (857 kB/s) to simply appear as 7018 And also needing 13043 kbps (1592 kB/s) to simply appear as 13043 Thanks people =LEFT(A1,FIND(" ",A1)-1) -- David Biddulph > Using Excel 97 > [quoted text clipped - 9 lines] > > Thanks people >=LEFT(A1,FIND(" ",A1)-1) >-- >David Biddulph WOW! thanks a lot... seems so simple now :-) >=LEFT(A1,FIND(" ",A1)-1) >-- >David Biddulph i HAVE a new problem though. Now Those 'new' numbers cannot be averaged, I can't run an average of those numbers it's gives a 'divide by zero' error as though I am not averaging real numbers? can you help again? thanks Change David's formula to this... =--LEFT(A1,FIND(" ",A1)-1) In a formula, multiplying a string value (what the LEFT function returns) by a number produces a number as a result. The -- does the same thing as multiplying the string by -1 twice (-1 times -1 is the same as +1, so the value is simply converted from a string to a number). You could also use 1* or 0+ instead of the -- and get the same unchanged conversion. Rick >>=LEFT(A1,FIND(" ",A1)-1) >>-- [quoted text clipped - 7 lines] > > thanks On Tue, 20 May 2008 12:08:25 -0400, "Rick Rothstein \(MVP - VB\)" <rick.newsNO.SPAM@NO.SPAMverizon.net> sayd the following: >Change David's formula to this... > >=--LEFT(A1,FIND(" ",A1)-1) OMG that worked like a charm. thanks to you and all else. You can either truncate the "kbps" part also: =LEFT(A1,FIND(" ",A1)-1) Or, if you are left with values like 1874 kbps you can extract an average over those cells with the *array* formula: =SUM(--LEFT(A1:A100,FIND(" ",A1:A100)-1))/COUNTA(A1:A100) Array formula, commit with Shift+Ctrl+Enter. Does this help? Kostis Vezerides > >=LEFT(A1,FIND(" ",A1)-1) > >-- [quoted text clipped - 7 lines] > > thanks |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.