Removiing decimals from an excel number

[Data Analyst]

Here is the issue: I have numbers like this one - 10000.01.01.200

I just want the prime number 10000. How can I do that? I tried Ctrl+F and
replace but is there a more efficient way?

Thanks!

 

[Gary''s Student]

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

 

[Gary Keramidas]

try this

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

 

[Data Analyst]

A thousand thanks

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

 

[Data Analyst]

What if I have Prime Numbers in the column..how can I keep them as is with
this formula?

 

[Data Analyst]

I still have error..

try this

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

--


Gary Keramidas
Excel 2003




.

 

[T. Valko]

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

That formula resturns the number as TEXT.

Try it like this to return the number as a NUMBER.

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

 

[Gary Keramidas]

you can try this and add biffs idea if you want a number instead of text

=IF(ISERROR(FIND(".",A2,1)),A2,LEFT(A2,FIND(".",A2,1)-1))

 

[T. Valko]

Improvement!

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

Don't need the -1

=--LEFT(A1,FIND(".",A1&"."))

 

[Gord Dibben]

That is not a number, it is text.

I would use text to columns delimited by decimal point and choose to skip
all but left column.


Gord Dibben MS Excel MVP