How 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

How to truncate digits?
 

=LEFT(A1,FIND(" ",A1)-1)
--
David Biddulph

"Robert Blass" wrote in message
...
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

How to truncate digits?
 

On Tue, 20 May 2008 16:41:30 +0100, "David Biddulph" <groups [at]
biddulph.org.uk sayd the following:

=LEFT(A1,FIND(" ",A1)-1)
--
David Biddulph


WOW! thanks a lot... seems so simple now :-)

How to truncate digits?
 

On Tue, 20 May 2008 16:41:30 +0100, "David Biddulph" <groups [at]
biddulph.org.uk sayd the following:

=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

How to truncate digits?
 

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


"Robert Blass" wrote in message
...
On Tue, 20 May 2008 16:41:30 +0100, "David Biddulph" <groups [at]
biddulph.org.uk sayd the following:

=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

How to truncate digits?
 

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

On May 20, 6:59 pm, Robert Blass wrote:
On Tue, 20 May 2008 16:41:30 +0100, "David Biddulph" <groups [at]
biddulph.org.uk sayd the following:

=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

How to truncate digits?
 

On Tue, 20 May 2008 12:08:25 -0400, "Rick Rothstein \(MVP - VB\)"
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.