Get simple, powerful tips for making spreadsheet information more readable and understandable in Excel for Mac 2016.
Excel has many built-in formats that you can use: Currency, Accounting, Date, Time, Percentage, etc. If you cannot find the right format, you can create a custom number format. Leading Zeros For example, you might have codes that consist of 5 numbers. Instead of typing 00041, simply type 41 and let Excel add the leading zeros. Enter the value 41 into cell A1. Select cell A1, right click, and then click Format Cells.
![Excel Excel](/uploads/1/2/5/5/125509486/815188939.jpg)
Select Custom. Type the following number format code: 00000. Note: Excel gives you a life preview of how the number will be formatted (under Sample). Result: Note: cell A1 still contains the number 41.
We only changed the appearance of this number, not the number itself. Decimal Places You can also control the number of decimal places. Use 0 to display the nearest integer value. Use 0.0 for one decimal place. Use 0.00 for two decimal places, etc. Enter the value 839.1274 into cell A1. Use the following number format code: 0.00 Add Text You can also add text to your numbers.
For example, add 'ft'. Enter the value 839.1274 into cell A1. Use the following number format code: 0.0 'ft' Note: remember, we only changed the appearance of this number, not the number itself. You can still use this number in your calculations. Large Numbers You can also control large numbers. Use one comma (,) to display thousands and use two commas (,) to display millions.
![Mac Mac](/uploads/1/2/5/5/125509486/233994666.jpg)
Enter the following values in cells A1, B1, C1 and D1: 1000000, 2500000, 81000000 and 700000. Use the following number format code: 0.0, 'M' Note: we used 0.0 for one decimal place and 'M' to add the letter M. Repeat Characters Use the asterisk (.) followed with a character to fill a cell with that character. Type Hi into cell A1. Use the following number format code: @.- Note: the @ symbol is used to get the text input. Colors You can control positive numbers, negative numbers, zero values and text all at the same time! Each part is separated with a semicolon (;) in your number format code.
Enter the following values in cells A1, B1, C1 and A2: 5000000, 0, Hi and -5.89. Use the following number format code: Green$#,##0);Red$(#,##0);'zero';Blue'Text:' @ Note: #,## is used to add comma's to large numbers.
To add a space, use the underscore ' followed by a character. The length of the space will be the length of this character. In our example, we added a parentheses ')'.
As a result, the positive number lines up correctly with the negative number enclosed in parentheses. Use two parts separated with a semicolon (;) to control positive and negative numbers only.
Use three parts separated with a semicolon (;) to control positive numbers, negative numbers and zero values only. Dates and Times You can also control dates and times. Use one of the existing Date or Time formats as a starting point. Enter the value 42855 into cell A1. Select cell A1, right click, and then click Format Cells. Select Date and select the Long Date.
Note: Excel gives you a life preview of how the number will be formatted (under Sample). Select Custom. Slightly change the number format code to: mm/dd/yyyy, dddd 6. Result: General note: a custom number format is stored in the workbook where you create it. If you copy a value with a custom number format to another workbook, it will also be available in that workbook.
I'm trying to create an Excel sheet to display MAC addresses in the following format - 00:00:00:00:00:00 However. The characters can be either letters or numbers. I've modified the 'hh:mm:ss' template to accomodate the format I need, but it will not recognize letters for the obvious reason. Is there a wildcard character that I could use in this format or has anyone created a format that would meet my needs? If I can pull this off, it could mean a promotion. Thanks in advance for any and all help.
Code: Private Sub WorksheetChange(ByVal Target As Range) Dim myText As String If Len(Target) 12 Then Exit Sub 'check for correct length If Target.Column 1 Then Exit Sub 'only Column A Application.EnableEvents = False myText = Left(Target.Text, 2) & ':' & Mid(Target.Text, 3, 2) & ':' & Mid(Target.Text, 3, 2) & ':' & Mid(Target.Text, 3, 2) & ':' & Mid(Target.Text, 3, 2) & ':' & Right(Target.Text, 2) Target.Value = myText 'put MAC string back into original cell Application.EnableEvents = True End Sub. How to use the above code: Press Alt-F11 to open the VBE. Press Control-R to open the Project Explorer.
Click 'Microsoft Excel Objects' for the file you're working on. Double-click the sheet module where you need this to work. Open the Code pane with F7. Paste the above code in. Press Alt-Q to close the VBE and return to Excel Hope that helps! Assumming your addresses are in column A, starting in row 2 Option Explicit Private Sub WorksheetChange(ByVal Target As Range) If Target.Column 1 Then Exit Sub 'column of interest If Target.Row 1 Then Exit Sub 'no multiple selections With Target 'ex: ae:12:e0:34:bb:00 Application.EnableEvents = False.Value = Left(.Value, 2) & ':' & Mid(.Value, 3, 2) & ':' & Mid(.Value, 5,2) & ':' & Mid(.Value,7,2)& ':' & Right(.Value,2) Application.EnableEvents = True End With End Sub This goes in the WorkSheet module HTH lenze.