 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2008Number format code
I can't seem to figure this one 21 1/2" vs. 21 " I am trying to create a custom format number code showing the inch hash marks after the number. My problem is when a whole number is input the space between the number and hash marks are annoyingly too far apart. Any help would be greatly appreciated. You could use conditional formatting using the a formula such as: =INT(A1)=A1 custom format as #\" and =INT(A1)<>A1 custom format as # ?/?\" 21 will be formatted as 21" and 21.5 as 21 1/2" Tyro >I can't seem to figure this one > [quoted text clipped - 5 lines] > between the number and hash marks are annoyingly too far apart. Any help > would be greatly appreciated. >You could use conditional formatting using the a formula such as: >=INT(A1)=A1 custom format as #\" and =INT(A1)<>A1 custom format as # ?/?\" >21 will be formatted as 21" and 21.5 as 21 1/2" > >Tyro That feature must be new for Excel 2007, as it is not available in 2003. It sounds neat! --ron Ron: As far as I know, those formulas should work in Excel 2003 too. But I don't have Excel 2003 installed, just 2007. Tyro >>You could use conditional formatting using the a formula such as: >>=INT(A1)=A1 custom format as #\" and =INT(A1)<>A1 custom format as # [quoted text clipped - 7 lines] > sounds neat! > --ron Thanks for the help, however this solution still returns the same results. I know that selecting a range of cells, right clicking, and creating a custom format is possible. I got advice in 2000 that worked. I am using 2003 version and Just cant seem to type in the right code..... 2003 allows for 3 conditions. As far as I remember, you can use formulas in all 3 conditions. Perhaps I'm wrong. The custom format codes are standard for Excel 2003 and 2007 - #\" and # ?/?" Nothing new here. These formats work fine. Tyro > Thanks for the help, however this solution still returns the same results. > I know that selecting a range of cells, right clicking, and creating a > custom format is possible. I got advice in 2000 that worked. I am using > 2003 > version and Just cant seem to type in the right code..... Correction, as usual: The custom format codes are standard for Excel 2003 and 2007: #\" and # ?/?\" Tyro > 2003 allows for 3 conditions. As far as I remember, you can use formulas > in all 3 conditions. Perhaps I'm wrong. The custom format codes are [quoted text clipped - 9 lines] >> 2003 >> version and Just cant seem to type in the right code..... I am aware These codes work 'fine' but visually . the '' marks are too far to the right when a whole number is input 21 ''. # ??/??'' works as well using the single tick mark (' lower case) Thanks for the effort and quick responses.. I'll just e-mail Chip Pearson and get the right solution. > 2003 allows for 3 conditions. As far as I remember, you can use formulas in > all 3 conditions. Perhaps I'm wrong. The custom format codes are standard > for Excel 2003 and 2007 - #\" and # ?/?" Nothing new here. These formats > work fine. > > Tyro >I am aware These codes work 'fine' but visually . the '' marks are too far >to the right when a whole number is input 21 ''. ># ??/??'' works as well using the single tick mark (' lower case) >Thanks for the effort and quick responses.. I'll just e-mail Chip Pearson >and get the right solution. What was the problem with the solution I posted yesterday? I'd like to know the issue so I can correct it. --ron >Ron: > >As far as I know, those formulas should work in Excel 2003 too. But I don't >have Excel 2003 installed, just 2007. > >Tyro Oh, the formulas work just fine; BUT Conditional Formatting does NOT offer the possibility of altering the number format in 2003 and previous. You can change the font and various colors, but not the number format. --ron Ron I'm getting too old. 64 soon. Memory fades. :( Oh well! Tyro >>Ron: >> [quoted text clipped - 10 lines] > the font and various colors, but not the number format. > --ron >I can't seem to figure this one > [quoted text clipped - 4 lines] >between the number and hash marks are annoyingly too far apart. Any help >would be greatly appreciated. You cannot do that in XL2003 and previous. (I don't know about XL2007 -- Tyro indicated it could be done). In XL2003, you could use an event triggered macro. For example, if your data to be formatted was in A2:A10 -- 1. Right click on the worksheet tab and select View Code. 2. Paste the code below into the window that opens. 3. Change AOI to the range where you want this formatting to occur. ==================================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim AOI As Range, c As Range Const F1 As String = "# ?/? \""" Const F2 As String = "# \""" Application.EnableEvents = False Set AOI = [a2:a10] For Each c In AOI If IsNumeric(c.Value) And Len(c.Value) > 0 Then If Int(c.Value) = c.Value Then c.NumberFormat = F2 Else c.NumberFormat = F1 End If End If Next c Application.EnableEvents = True End Sub =============================== --ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.