How to truncate digits?

[Robert Blass]

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

 

[David Biddulph]

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

 

[Robert Blass]

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

WOW! thanks a lot... seems so simple now

 

[Robert Blass]

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

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[vezerid]

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

 

[Robert Blass]

Change David's formula to this...

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

OMG that worked like a charm.

thanks to you and all else.