Number format code

  • Thread starter Thread starter Woodbutcher
  • Start date Start date
W

Woodbutcher

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
 
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
Click to expand...

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
 
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
 
Correction, as usual:

The custom format codes are standard for Excel 2003 and 2007: #\" and #
?/?\"

Tyro
 
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
Click to expand...

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
 
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.
Click to expand...

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
 
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.
 
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.
Click to expand...

What was the problem with the solution I posted yesterday? I'd like to know
the issue so I can correct it.
--ron
 
Back
Top