| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removiing decimals from an excel number
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removiing decimals from an excel number
=LEFT(A1,FIND(".",A1)-1)
-- Gary''s Student - gsnu201001 "Data Analyst" wrote: 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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removiing decimals from an excel number
A thousand thanks:)
"Gary''s Student" wrote: =LEFT(A1,FIND(".",A1)-1) -- Gary''s Student - gsnu201001 "Data Analyst" wrote: 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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removiing decimals from an excel number
What if I have Prime Numbers in the column..how can I keep them as is with
this formula? "Gary''s Student" wrote: =LEFT(A1,FIND(".",A1)-1) -- Gary''s Student - gsnu201001 "Data Analyst" wrote: 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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removiing decimals from an excel number
=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) -- Biff Microsoft Excel MVP "Data Analyst" wrote in message ... What if I have Prime Numbers in the column..how can I keep them as is with this formula? "Gary''s Student" wrote: =LEFT(A1,FIND(".",A1)-1) -- Gary''s Student - gsnu201001 "Data Analyst" wrote: 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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removiing decimals from an excel number
Improvement!
=--LEFT(A1,FIND(".",A1&".")-1) Don't need the -1 =--LEFT(A1,FIND(".",A1&".")) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... =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) -- Biff Microsoft Excel MVP "Data Analyst" wrote in message ... What if I have Prime Numbers in the column..how can I keep them as is with this formula? "Gary''s Student" wrote: =LEFT(A1,FIND(".",A1)-1) -- Gary''s Student - gsnu201001 "Data Analyst" wrote: 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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removiing decimals from an excel number
try this
=LEFT(A1,FIND(".",A1,1)-1) -- Gary Keramidas Excel 2003 "Data Analyst" wrote in message ... 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! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removiing decimals from an excel number
I still have error..
"Gary Keramidas" wrote: try this =LEFT(A1,FIND(".",A1,1)-1) -- Gary Keramidas Excel 2003 "Data Analyst" wrote in message ... 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! . |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removiing decimals from an excel number
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)) -- Gary Keramidas Excel 2003 "Data Analyst" wrote in message ... I still have error.. "Gary Keramidas" wrote: try this =LEFT(A1,FIND(".",A1,1)-1) -- Gary Keramidas Excel 2003 "Data Analyst" wrote in message ... 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! . |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removiing decimals from an excel number
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 On Wed, 10 Feb 2010 09:02:01 -0800, Data Analyst wrote: 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| problem with number format with 2 decimals? | Excel Worksheet Functions | |||
| Number of Decimals Excel can Handle | Excel Discussion (Misc queries) | |||
| Whole Number and Decimals | Excel Discussion (Misc queries) | |||
| Displaying decimals that go in to a given number | Excel Discussion (Misc queries) | |||
| Merging Excel data to word, keeps including all decimals on number | Excel Discussion (Misc queries) | |||
Hybrid Mode
