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!
0
Utf
2/10/2010 5:02:01 PM
excel.misc 78881 articles. 5 followers. Follow

9 Replies
843 Views

Similar Articles

[PageSpeed] 22

=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!
0
Utf
2/10/2010 5:05:01 PM
try this

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

-- 


Gary Keramidas
Excel 2003


"Data Analyst" <DataAnalyst@discussions.microsoft.com> wrote in message 
news:7162CA14-9130-4DAE-AA8E-847FCE9F1402@microsoft.com...
> 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! 

0
Gary
2/10/2010 5:06:17 PM
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!
0
Utf
2/10/2010 5:09:01 PM
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!
0
Utf
2/10/2010 5:25:01 PM
I still have error..

"Gary Keramidas" wrote:

> try this
> 
> =LEFT(A1,FIND(".",A1,1)-1)
> 
> -- 
> 
> 
> Gary Keramidas
> Excel 2003
> 
> 
> "Data Analyst" <DataAnalyst@discussions.microsoft.com> wrote in message 
> news:7162CA14-9130-4DAE-AA8E-847FCE9F1402@microsoft.com...
> > 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! 
> 
> .
> 
0
Utf
2/10/2010 5:53:02 PM
>>=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" <DataAnalyst@discussions.microsoft.com> wrote in message 
news:CB80BFB7-6BD5-4315-B964-8D9EB26538AF@microsoft.com...
> 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! 


0
T
2/10/2010 5:59:52 PM
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" <DataAnalyst@discussions.microsoft.com> wrote in message 
news:BBE73890-8D8B-42F8-B1D4-CDE87923874A@microsoft.com...
>I still have error..
>
> "Gary Keramidas" wrote:
>
>> try this
>>
>> =LEFT(A1,FIND(".",A1,1)-1)
>>
>> -- 
>>
>>
>> Gary Keramidas
>> Excel 2003
>>
>>
>> "Data Analyst" <DataAnalyst@discussions.microsoft.com> wrote in message
>> news:7162CA14-9130-4DAE-AA8E-847FCE9F1402@microsoft.com...
>> > 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!
>>
>> .
>> 

0
Gary
2/10/2010 6:04:23 PM
Improvement!

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

Don't need the -1

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

-- 
Biff
Microsoft Excel MVP


"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:umInZrnqKHA.5936@TK2MSFTNGP04.phx.gbl...
>>>=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" <DataAnalyst@discussions.microsoft.com> wrote in message 
> news:CB80BFB7-6BD5-4315-B964-8D9EB26538AF@microsoft.com...
>> 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!
>
> 


0
T
2/10/2010 6:06:58 PM
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
<DataAnalyst@discussions.microsoft.com> 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!

0
Gord
2/11/2010 12:10:24 AM
Reply:

Similar Artilces:

Excel and MRP. Scheduling...
Hi everyone. Does anyone here uses Excel sheets to calculate MRP, Scheduling, and s on? Need to exchange some ideas... Cyas -- Message posted from http://www.ExcelForum.com .. -- Message posted from http://www.ExcelForum.com ...

include the ability to price standard items to three decimals
It would be nice to be able to discount items to three decimals. Example, 3 for 2.99= .997 each. Or 12 for $1.00= .083 each. Currently we cannot do this. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgb...

Loss of referenced data (Titles) when upgrading from Excel 2000 to 2007
I have many instances of the same type of issue, so will provide one example. A solution for one should fix all... I have a chart data sheet that 20+ charts reference every month. When creating the titles for the charts, it was necessary to refer to the month the chart represents. This information is located in one cell on the data sheet. In Excel 2000, all one had to do was have the focus on the chart, type in "=" and reference the cell the date is in. The data in the cell would then show on the chart as basically a text box and I could manipulate other words ar...

Excel to Html
I have to export an Excel file to html. I'am already doing it but I wonder if it's possible for Excel to produce a separeted css file instead of an included one. This to produce a lighter file. thanks Maurizio ...

Hyperlink to excel worksheet
Hello Everyone This is my first post so I hope you can help me..... I want to create a hyperlink file from an intranet site to an excel file on our organisation server. The excel file is quite large with more than 25 worksheets. I would like to create a separate hyperlink to each worksheet in the excel file. Unfortunately I can only create a hyperlink to my excel file that opens at the last point I saved the excel file. I hope that makes sense and anyone who can help me would be much appreciated Jamie from Australia --- Message posted from http://www.ExcelForum.com/ ...

excell cell there is figure Rs.10545,, how to conver in words
i wANT TO COVERT EXCELL CELL VALUE SAY RS. 10545/- and i want result TEN THOUSAND FIVE HUNDRED AND FOURTY FIVE . Hi, You can find the code to do this at: http://support.microsoft.com/kb/213360 If this helps, please click the Yes button. -- Thanks, Shane Devenshire "SANTOSH" wrote: > i wANT TO COVERT EXCELL CELL VALUE SAY RS. 10545/- > > and i want result TEN THOUSAND FIVE HUNDRED AND FOURTY FIVE . ...

decimal places in a form
Is there a way to set decimal places to show say when i type in 22.29 (and dosent round) but when I type in 22 it dosent show 22.00? -- Message posted via http://www.accessmonster.com Usually when this happens the data type is the problem. Integers (long or short) are ... well integers and integers are only whole numbers. If that is not the case then you may need to look at the properties of where you are viewing the number. -- Joseph Meehan Dia 's Muire duit "stephendeloach via AccessMonster.com" <u32891@uwe> wrote in message news:73fcb86c294cd@uwe.....

Cell formating in Excel?
Is there a way to have a scroll bar in a single cell? No. -- Best Regards, Luke M "pmarina" <pmarina@discussions.microsoft.com> wrote in message news:5D25B665-B7B8-477D-AAF8-80A347FFA0DD@microsoft.com... > Is there a way to have a scroll bar in a single cell? ...

How to get a long column of numbers to wrap and print on 1 page?
I have a long column of numbers that we add to daily. This column needs to be sorted in ascending order and then printed. I don't know of a way to sort multiple columns together so that is why it is one long column. Is there a way to get it to print as multiple columns across a page so as to not have numerous pages with only 1 column on each? Thanks. take a look here: http://www.mvps.org/dmcritchie/excel/snakecol.htm In article <3880FBB4-BF95-4A82-B98A-519C79BF1A34@microsoft.com>, Sue <Sue@discussions.microsoft.com> wrote: > I have a long column of numbers th...

Inserting Columns in Excel
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When I select a column or columns to enabalize inserting additional columns, nothing happens. I can't get columns to insert in either the page layout view or the normal view. Why is something so simple not working? All I get is a little paintbrush icon that wants me to decide if I want the formatting of the adjacent column or not. Please help, it can't be that hard! No offense, but my guess is that you're misinterpreting the feature & perhaps using it on a blank sheet. The command does not increase...

Naming columns in Excel
I am trying to set up a database in Excel. I need to change the columns (A, B, C,...etc) to name, date, ssn, etc. I am having trouble and dont know how to do it. PLEASE HELP! I want to be able to have each column name to reflect the information that will be put in that respective column. Thank you for your help Hi you can't change the column headers. The best you could do is use the first row as heading row "Peggy" wrote: > I am trying to set up a database in Excel. I need to change the columns (A, > B, C,...etc) to name, date, ssn, etc. I am having troubl...

Excel function names in different languages
Hi all, One of the most annoying "features" in excel has been that excel only recognizes the function names in the language the excel is for. I myself have Finnish (language from Finland) excel at home and English excel at work. Now you can believe the mess I'm in when I have used excel at work whole day using english functions and start to use excel at home. That's just not very convinient. Is there any way to enable i.e. Finnish excel to understand standard english functions? Is there even a reference list out there where I could check what any given english function nam...

Excel 2007 Crashing Problem #3
My company has been riddled with Excel 2007 crashing since we started migrating to the 07 suite. Two users who prominently use Excel for their job tasks have seen many issues mainly when converting .xls documents to .xlsx. The common theme i have seen here has been file size and weather or not the file contains links. Both users also work heavily with graphs. My question is, has there been reported problems with converting large excel files to the new format? Or has there been a graphing problem? ...

Outlook excel
When I double click an Excel file in Outlook, I get the message "The system cannot find the file specified". However, for Work, PDF or other files, they work just fine. Can anyone tell me what the problem is? jdwms@usa.net have you got Excel installed? "joey williams" <anonymous@discussions.microsoft.com> wrote in message news:008b01c3aa04$c92174f0$a301280a@phx.gbl... > When I double click an Excel file in Outlook, I get the > message "The system cannot find the file specified". > However, for Work, PDF or other files, they work just > ...

Formula Excel
Wondering if someone would be able to help me with this: A B C Year Year Service 01-Sep-89 30-Jun-10 = 21 Years Teachers From September 1, 1989 to June 30, 1990 = 1 Year of Service. I know it only 10 months, but that is their year. Is there a formula that could caculate the number of years from September 1, 1989 to June 30, 2010. -- Newfie The general formula for years between dates is =Year(latest date) - Year(earlier date) and 2010-1989 = 21 years. So that might work for most cases? A complication arises if they begin, but do...

How do I doanload Excel 97?
I'm taking a accounting class on line and our teacher say we can download free templates. I can download the templates but I can't edit or do the work to email to my teacher. Every time I go to open and edit it tells me that microsoft excel is not downloaded correctly. Help me please because I have to turn my work in. Hi you need to actually purchase excel 97 - templates are just predesigned workbooks. However, having said that, the latest version of Excel is 2003 (so there's excel 2000 & 2002 between 97 & 2003). The latest version can be purchased from just a...

Excel 2003 to Excel 2007 graphs
I have a bunch of Excel 2003 graphs that are now being updated with new data. In the course of this, we are also saving as Excel 2007 macro-enabled files (.xlsm). Any time the file is saved it has a message re: invalid name or range and says it will change the name to "_CHARTA_xxyy" for example. You are prompted to say "okay" or "okay to all". If I follow it through, there are about 82 instances of this, so I figure it has to do with components of the graphs on the 2 worksheets. There are a total of 4 graphs. Any quick solutions to this compatibility ...

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! =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! try this =LEFT(A1,FIND(".",A1,1)-1)...

Formatting Excel
I have a table in excel, all of the information in this table is linked to AutoCAD. I have added an example below: Example 1 NO. NAME BOTTOM BOTTOM TOP TOP MAT FIN MAT FIN 101 CORRIDOR PLASTIC SMOOTH RUBBER SMOOTH 102 CORRIDOR PLASTIC SMOOTH RUBBER SMOOTH I am trying to get Example 1 to look like Example 2 without having to re-enter everything. It is the same info. but a different layout. I tried a Pivot Table but was not successful. Does anyone have any ideas? Example 2 NO. NAME BOTTOM TOP 10...

Error opening Excel File
Hi, I'm currently having these days an error while opening an excel sheet. The sheet is shared among five users. When one tries to open it, a message indicates that the file is locked for editing by another user and after checking with the user, I understand that he has already closed the file but the latter remains locked. Can someone help? ...

Crash on startup of Excel from Office 2008
Hi folks, I was wondering if I could get some help with an issue I have with Excel from Office 2008. Upon launch, Excel crashes with the following error report (attached). This is after a clean install of office 2008. I've tried removing office completely (including the folders that the uninstaller does not remove) and reinstalling it with no success. Excel is the only program that's exhibiting this problem. Additionally, excel does NOT crash if it is started by double-clicking an existing xls file. However, even once it is running choosing File -> New Workbook c...

cannot open excel book
I am unable to open a specific excel book on my pc, other excel books will open and this specific excel book will open on other pc's. Can you help me solve this problem ? When you try to open the workbook on your PC, exactly what happens? D you get an error message? If so, what does it say -- Message posted from http://www.ExcelForum.com ...

max number
may anyone tell me how to find the largest number within a heap of numbers?? On Mon, 24 Nov 2003 00:44:43 +0800, "vivats" <vivats@netvigator.com> wrote: >may anyone tell me how to find the largest number within a heap of numbers?? > =MAX(Heap) --ron =max(rangeof heap) vivats wrote: >may anyone tell me how to find the largest number within a heap of numbers?? > > > > Assuming the "heap" is a range........ =MAX(A1:A10) Gord Dibben XL2002 On Mon, 24 Nov 2003 00:44:43 +0800, "vivats" <vivats@netvigator.com> wrote: &...

search work book in excel 97
how do i search a workbook in excel 97? in excel xp i click the first sheet then shift click the last sheet to select all the sheets, then click find, enter the info and it searches every sheet in the book. I have excel 97 at work and the same procedure only searches the first sheet. is there a different method. in 97 thanks for any help steve Hi this is a change in the later Excel versions. Excel 97 does not support this workbook search.But you may try Jan Karel Pieterse's FLEXFIND.XLA from http://www.bmsltd.ie/mvp/ -- Regards Frank Kabel Frankfurt, Germany Steve Goodrich wrote: &g...

SOP NonInventory Unit Cost 6 Decimal Places
Is there any simple way to increase the currency decimal places to greater than the current highest value of 5? Thanx Scott, I don't believe there is any easy or even moderately difficult way to do this, as the field types used to store amounts in GP only go up to 5 decimal places. -- Victoria Yudin Dynamics GP MVP Flexible Solutions - home of GP Reports http://www.flex-solutions.com/gpreports.html blog: http://www.victoriayudin.com "Scott Rutledge" <sersoar@hotmail.com> wrote in message news:E102B81B-8555-400E-843C-240C58794294@microsoft.com... > Is there any...