|
Format Cells in The General (default) Format in Excel Number Formatting
This free Excel macro formats a selected cell or range of cells to the General number format in Excel. This is the default number format and contains no special or specific formatting.
This is a great macro when you need to remove special number formatting from a large selection of cells quickly. This macro allows you to see exactly what is entered into the cell in Excel instead of a pre-formatted value.
Where to install the macro: Module
Excel Macro to Format Cells in The General (default) Format in Excel Number Formatting
Sub Format_Cell_General()
Selection.NumberFormat = "General"
End Sub
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Using Excel 2010 - I have a large workbook with multiple worksheets - been using it succesfully every day for a long time. All of a sudden every empty cell, and any cell not specifically formated in every worksheet has a default cell format of Time. Any new workbook created is OK.. defaults to 'General' - But, if I add a new blank worksheet to this particular workbook it defaults to 'Time' format.
How did the default cell format for this workbook become set to 'Time' from 'General' and how do i fix it?
Thanks in advance for any guidance.
I have been working in a worksheet with 10 tabs, and suddenly all of my numbers turned to long date format. I manually changed them all back to what they were. General, Currency, %, etc. but the default seems to be stuck in the long date format. When I create a new tab it is automatically all long dates. Very strange.
Oh, and the other weird thing is when I send it to other people, some people see the correct formats and when other's open it the numbers all go back to the long date format. VERY Frustrating.
Would love to get some advice.
I have a friend who is having an issue with numbers in cells formated as "General" being unexpectedly converted to date formats. When it happens, it happens with ALL numbers in a workbook on all individual sheets.
I expect this to happen when a number or text is formatted somewhat like a date. However, this is happening to whole numbers, decimals etc.
i.e., 1 becomes 1/1/1900, 120 becomes 4/29/1900
I can't seem to wrap my head around why this could be happening.
They are using Excel 2007, and it is a problem that has started happening recently around all kinds of XL documents. I understand that the underlying values are still correct, but when it happens to a document that has hundreds of rows/columns and dozens of sheets, it can be incredibly tedious to fix. Especially since the documents are protected, and users are not permitted to change the format of cells.
Any help or insights are appreciated.
Hello
I can't search the forum. I get this message.
Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 71 bytes) in /home/eforum/public_html/search.php on line 1155
any suggestions?
Hello everyone-
I'm having a bit of an issue with a colleagues Excel and was hoping some of you might be able to help. Here's the information..
We are using a shared spreadsheet that each of my colleagues can pull up on their own local computer. The error we are seeing is only occuring on one persons local machine.
Let's say she tries to type the number 1234 into a cell. For her, it generates as 12.34 with a decimal automatically placing itself in the middle. By checking format cells, this is the way her general tab is set up. If we try and back down the decimal places by two spots 1234 will generate a rounded response - in this case, 12, which is no good if she is trying to view a 4 digit number.
We have tried formating all sorts of different ways, removing decimals points, dragging correctly formated cells into these boxes in an attempt to change formating all to no avail.
We were able to correctly input a 4-digit number by chaning the format of the cell to text, then typing in 1234, and converting it to a number cell when the error (!) shows up. It's nice that there is a work around, but for the volume of cells that we are working with it is quite the task to manually change every single one one at a time.
Any suggestions? Thanks so much.
copied info from syspro report, pasted into excel, split cell info into more
collums. numbers are in general format, cant add them, i cant change the
format by formating the cell to numbers is there a way to reformat?
I have an Access tool to extract data from many different surveys with many modules all containing many columns and rows. The one thing in common is that the data is either a numeric value or a date. The data is exporting into Excel as general. I need it to be numeric or date format specifically. I know several ways to somewhat globally convert numbers/dates as text to numbers/dates (copy the number 1 and paste special value/multiply). What I want to do via a macro is to identify those columns that are supposed to be dates and format them as dates before doing the the copy trick mentioned above. The column names are different in each module and in different orders. Is there a way to identify a column as containing a date so the column can be formatted then when I do copy '1', paste special, values, multiply the dates stay in date format and numbers are numbers. I have manually selected date columns and formatted them to date format prior to doing the Copy Paste Value trick and it works. The issue is how to quickly find the date columns. Is there a way to write a macro that checks to see if the contents of the cell looks like a date then format the cell to be in date format?
Thanks
|
|
