format a column

I have a simple question to ask, I have a column in Excel is number like
142000, I want to format the whole column numbers without the two ending
0s,

change 142000 to 1420, how can I do that?

Thanks


-- 
xcao
------------------------------------------------------------------------
xcao's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14538
View this thread: http://www.excelforum.com/showthread.php?threadid=471821

0
9/29/2005 5:04:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1307 Views

Similar Articles

[PageSpeed] 22

Enter 100 in an empty cell and copy it.  Then, select your range of numbers 
you want to change, Right Click and go to Paste Special, and select Divide.

-- 
Regards,
Dave


"xcao" wrote:

> 
> I have a simple question to ask, I have a column in Excel is number like
> 142000, I want to format the whole column numbers without the two ending
> 0s,
> 
> change 142000 to 1420, how can I do that?
> 
> Thanks
> 
> 
> -- 
> xcao
> ------------------------------------------------------------------------
> xcao's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14538
> View this thread: http://www.excelforum.com/showthread.php?threadid=471821
> 
> 
0
9/29/2005 5:14:01 PM
Thank you, it's helpful


-- 
xcao
------------------------------------------------------------------------
xcao's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14538
View this thread: http://www.excelforum.com/showthread.php?threadid=471821

0
9/29/2005 8:59:54 PM
Reply:

Similar Artilces:

Freeze Formatting
I'm trying to develop a way to freeze the formatting of a cell or group of cells (lines and shading). What I need to be able to do is click and drag the contents of a cell to another cell while maintaining the original formatting of the originating and destination cell. -Shawn Maybe you could catch the change, save the new formula/value, do an undo and then reapply the formula/value. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myFormulas As Variant On Error GoTo errHandler: myFormulas = Target.Formula With Application .Enabl...

Conditional Formatting #83
Using Conditional Formatting, how do I highlight cells that have integers & those that have decimals? -- Dr. Sachin Wagh MBBS, DHA, DPH Select the cells that you want to format Choose Format>Conditional Formatting From the first dropdown, choose Formula Is In the formula box, enter a formula that refers to the active cell: =B2/INT(B2)=1 Click the Format button Select the formatting you want for integers, click OK Click Add From the first dropdown, choose Formula Is In the formula box, enter a formula that refers to the active cell: =B2/INT(B2)<>1 Click the Format butto...

Conditional Formating based on a "S"
I have looked at all the forum msgs and at ritches webpage (http://www.mvps.org/dmcritchie/excel/condfmt.htm) I want to have an entire row bolded when there is an "S" in the Q column. Any advice or help is much appreciated. Thanks, Wayne Wayne, Select your cells (to be bolded). The active (white) cell should be in row 2 for the following formula. Format - Conditional Formatting - Formula is: =$Q2="S" Adjust the 2 in $Q2 to match the row of the active cell of the selection. -- Earl Kiosterud mvpearl omitthisword at verizon period net -----------------------------...

Add Chart Columns
I can not successfuly add a new column to my Weight chart. I can re-build the entire chart. The Chart is a simple column chart with date on the X axis and a column for daily weight on the Y axis. This tutorial: http://office.microsoft.com/en-us/excel/HP051984721033.aspx#DifferentData Talks about different colored sizing handles that can be used to drag chart data to the chart area. It does not explain how to acquire the colored sizing handles. Any help to help me find way to add chart data will be appreciated. Office 2003 Thank The chart has to be embedded on the data sheet to see the ou...

Format of a cell
I have a problem when I read the ":" colon as a separator from an excel or text file that uses it as a separator. the example I have is that when the colon separator is followed by a number, say for example : 35, the number appears on the cell as 35:00:00 which is a time format. It seems that Excel takes the separator as a time format for the cell and it does not allow change of the cell's format not even to a simple text format. Even when the formatting of the cell is changed to a general format it generates a decimal number that is a multiplier of 24. If the value i...

Excel Column References
My spread sheets have spontaneously switched from giving column refs as letters to numbers. Instead of A, B, C etc they now show 1,2,3.......... Why has this happened and, more to the point, how can I correct it? Thanks in advance for advice Nic <Tools> <Options> <General> tab, *Uncheck* R1C1 reference style. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Nic Siddle" <NicSiddle@nsiddle.freeserve.co.uk> wrote in message news...

Formula
There isa command to allow a formula to refer to a number in a cell to adjust the column that is used in the formula. BUT I CAN"T FIND IT! If I want the formula to be using cell A11, I want to have 11 in a cell and have it refer to whatever to A11. If I have 12 in the cell then I want it to refer to A12 instead. Sorry, I am not explaining the scenario very well. Help. MD -- mdalby ------------------------------------------------------------------------ mdalby's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7055 View this thread: http://www.excelfor...

How Do I find distinct values in a Column #2
Hello, I work in the Resourcing Dept. i have to prepare a list of candidates that were interviewed. I have to consolidate lists from four offices. I have come across duplicate candidates entries after consolidating the worksheets. Can anyone tell me how do I find out duplicate values. Thanks I suggest two steps: 1. combine the four lists into a single column and sort by name. This will indicate duplicated items and how many duplicates exist. 2. Insure that there is some kind of label cell at the top of the column. then pull-down Data > Filter > Advanced Filter... and check...

Formatting concatenated text
It's easy to concatenate two text strings: =concatenate("Sales: ", "u last year") would produce "Sales: up last year". But I want to underline the word "Sales" in the final text string just not any of the other words or characters. Can this be done? Thanks. Tro -- Message posted from http://www.ExcelForum.com Hi this is not possible with formulas. Formulas can only return values but not change formats -- Regards Frank Kabel Frankfurt, Germany > It's easy to concatenate two text strings: =concatenate("Sales: ", "up >...

Macro to auto save file using the format YYMM
Hi, I recorded a macro that automatically saves a file then change the formula in cell A1 to today's date, ie =today(), then save the file. I have a problem with the last part...is there any way I can set the macro such that the file will be saved as "c:\YYMM ABC" where 'YYMM' is the format of the date (probably can be taken from cell A1) and 'ABC' is my own file name? Thanks! You can get the date right in the code: dim myFileName as string myfilename = "C:\" & format(date,"yymm") & " ABC.xls" activeworkbook.saveas fi...

Too few columns
Is there no way to get more columns by consolidating or something like it? -- Giggi You could upgrade to Excel 2007. There are significantly more columns than 2003 in it. Regards, Eddie http://www.HelpExcel.com Giggi, 256 is all there is, but excel 2007 will have 16K -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Giggi" <Giggi@discussions.microsoft.com> wrote in message news:C2CCAE69-33D8-4FC1-B3D5-037E1145EA3E...

Columns Names are missing
Funny Problem CRM Dev Server = Win2000 CRM Production Server = Win2003 I Did Some Form Customization on the Dev server for Testing and user Approval for the Production server I add some new Schema Fields and added the fields to the Forms I than Exported it from the Dev Server. Imported it to the Production Server, did the Publishing and run iisreset ALL OK and great BUT When CRM users open CRM the Columns Names are missing And if I go in to System Customization No names in the Fields Appears And If I Want to ADD new Fields or Columns it gives Me an Error (IE Script E...

freezing columns labels while sorting columns
... I need to sort names in columns, but while doing that my columns labels are also being sorted. How to avoid that? Excel 2003 In Sort Dialogue Box you can see an option button "Header Row" under "My Data Range As" click it. Excel 2007 In Sort Dialogue Box you can see a check box "My data has headers" in the Right corner near to Close Button in the Sort Dialogue box. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Stan" wrote: > .. I need to sort names in columns, ...

Cell Format #11
I have a situation I do not understand, maybe someone can give me some direction. I have received an invoice from one of our suppliers via email. They provide a way to translate there invoice into a comma separated value file. When I translate to excel I get a column with 756:00:00 in a cell. If I look at the bar at the top of the screen I have the value" 1/31/1900 12:00 PM " The result I am trying to get is numeric 756 . Does anyone have a suggestion on how I can get to the result I want?? Thank you in advance. Greg Bonsour=AE Greg avec ferveur ;o))) vous nous disiez :...

CRM 4.0: Report Wizard - Query execution failed for data set 'DSMain'.
How come the following report made by Report Wizard fail ? An error has occurred during report processing. Query execution failed for data set 'DSMain'. The column 'accountid' was specified multiple times for 'account0'. The report is designed as follows: New Report, Start a new report, Reportname = "test" Primary record type : Accounts Use saved view: Active Accounts Add a single column = AccountName All the rest is default choices. Changing saved view to My Active Accounts yields the same result. Changing saved view to Accounts: Responded to Campaingns ...

IF, AND for different Columns
I have a worksheet for a meeting that I am trying to get to where I can use the formula's and it will calculate as soon as the data is pasted from another report. I need the following: Column E will have the following options: BUY STK BUY/STK STK/BUY FRT Column F will have the following options: CAWPHY CAWPB CFWPHY CFWPB FRT (plus way more to list) The total of each Row is listed in Column J So, if Column E has "BUY" and Column F has "CAWPHY", I want the sum which is in Column J to be in cell E39. If Column E has "STK" an...

Default formatting of an inserted bookmark
Please assist. I have several titles in appendices that I refer to in a document (Appendix 2.1, Appendix 4.2, Appendix 4.7, etc.). The appendix headings have a different style associated with them than the body of the text. When I insert the bookmark reference to the appendix, the body of my text adopts the style of my appendix heading. This is not desirable. (The path I’m using is: Insert/Reference/Cross-Reference/Reference type: Bookmark/Insert reference to: Bookmark text with the Insert as hyperlink checked. While I can reapply formatting, I will be using this document a...

inserting line that runs between columns in a table
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi, <br> I want to insert a line (really an arrow) within one row that extends between more than one column in a table. <br><br>Along the top of the table I have months and I want to show than an activity begins in one month and extends for several months -- an arrow would be just the thing. <br><br>When I try to extend the line beyond the cell, it simply won't go any further. <br><br>Thanks, <br> Karen There is no specific provision for that in Word. Your...

Font Formats
When I type a particular word (MERLIN) into a cell in uppercase, it reverts to lower case (Merlin). Why, and how do I stop it? On Fri, 30 Jan 2004 12:21:06 -0800, "Enti" <coline@palletline.co.uk> wrote: >When I type a particular word (MERLIN) into a cell in uppercase, it reverts to lower case (Merlin). Why, and how do I stop it? Take a look in Tools -> AutoCorrect. Chances are that there's an entry for the word MERLIN which converts it to proper case. Jsst delete that entry. --------------------------------------------------------- Hank Scorpio scorpionet who hate...

Formatting/Page Break/Printing Issue
I set up a new excel file to match a 33 page hard copy document, and spaced the rows so that my document put the right text on the right pages. The text in each row spans past the columns that are set to print, however the print preview still looks correct (not cutting off any words, even though in a page break preview, the ends of each line fall in the grey area). When I copy this sheet into an existing excel file, the pages all change, and the text is cut off on the right side of the page. Any ideas? Thanks ...

format differnet widths columns and rows for different pages
I would like to know how you can have different formatting for row and colunm widths on different pages; is this possible and if so how do you do it?? You could use VBA to change the column.width for a worksheet..... if you were talking about that? -- Dnereb ------------------------------------------------------------------------ Dnereb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26182 View this thread: http://www.excelforum.com/showthread.php?threadid=486499 By "pages" do you mean printed pages on one worksheet? On each Excel worksheet th...

Graph formatting
I would like to shade the area on a line graph between 10-80 different from below 10 and above 80. How can this be accomplished? http://peltiertech.com/Excel/ChartsHowTo/HorizBands.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Mike Williams" <Mike Williams@discussions.microsoft.com> wrote in message news:22145446-3406-4186-BA7B-031BF3AA2EA3@microsoft.com... >I would like to shade the area on a line graph between 10-80 different from > below 10 and above 80. How ...

Text import
Hi I have a text file which I import into Access table using transfertext method. everything is okay except few date columns are in ddmmyyyy format In some of the columns I have a date in the follwoing format ddmmmyyyy (i.e 13Oct2007) - without any slash or hyphen. How can I get this in proper data format 10/13/2008 (mm/dd/yyyy) either at the time of importing or after importing get this proper date in a different column. Aslo, I have some dollar amount columns and signage (negative or positve) is given in a separate column. So all the dollar amounts are abosolute value. Based on the...

Automatically Fill Empty Cells in a Column
Is it possible to automatically fill empty cells in a column with the data in the cell above it? Hi Select the column / range. Then Edit / Go to / Special / Blanks. This will select all of the blank cells in the range. Type = and hit the up arrow and then type Ctrl Enter. To fix these values use Copy then Paste Special / Values. -- Andy. "Deignan" <deignan@ncsievents.com> wrote in message news:D2D1B200-92F6-44C9-A929-E9C66888C671@microsoft.com... > Is it possible to automatically fill empty cells in a column with the data in the cell above it? ...

custom cells format
I want to format a cell so i can transform 1256453 in 125.56. How can i format a cell so i can view first number as 125.56? Thanks for your help. You have a couple of replies to your other posts. puiuluipui wrote: > > I want to format a cell so i can transform 1256453 in 125.56. How can i > format a cell so i can view first number as 125.56? > Thanks for your help. -- Dave Peterson Go to Format -->Cell -->Number-->Custom There you type 0.00, You get the results as desire Sanjeev Agarwal "puiuluipui" wrote: > I want to format a cell so i can tra...