Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 8th 08, 07:47 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 47
Default Display feet inches and fraction

I would like to be able to input 11 3 1/2 and have the cell display as 11'-3
1/2" Is this possible?



  #2   Report Post  
Old February 8th 08, 08:35 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2008
Posts: 1
Default Display feet inches and fraction

I would like to be able to input 11 3 1/2 and have the cell display
as 11'-3 1/2" Is this possible?


Right-click the sheet tab for the sheet you want this functionality on and
select View Code on the popup menu that appears. Copy/Paste the following
into the code window that opened up in the VBA editor...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim TargetPattern As String
TargetPattern = Target.Value
For X = 1 To Len(Target.Value)
If InStr(" /", Mid$(TargetPattern, X, 1)) = 0 Then
Mid$(TargetPattern, X, 1) = "#"
End If
Next
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Value Like TargetPattern Then
Target.Value = Replace(Target.Value, " ", "'-", , 1) & """"""
End If
Whoops:
Application.EnableEvents = True
End Sub

If you enter a number in the format you showed (with only single spaces
between the numbers), then it will be reformatted the way you want.

Rick

  #3   Report Post  
Old February 11th 08, 07:11 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 47
Default Display feet inches and fraction

Rick,

Thanks for your help. I input the feet then a space, inches then a space
and then the fraction. The code worked except that I would get two inch
marks at the end of the dimension. When I removed two of the six inch
(quotation) marks toward the end of the code, only one inch mark shows up.
This seems to work for what I need.

Thanks,

Homer

"Rick Rothstein (MVP - VB)" wrote:

I would like to be able to input 11 3 1/2 and have the cell display
as 11'-3 1/2" Is this possible?


Right-click the sheet tab for the sheet you want this functionality on and
select View Code on the popup menu that appears. Copy/Paste the following
into the code window that opened up in the VBA editor...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim TargetPattern As String
TargetPattern = Target.Value
For X = 1 To Len(Target.Value)
If InStr(" /", Mid$(TargetPattern, X, 1)) = 0 Then
Mid$(TargetPattern, X, 1) = "#"
End If
Next
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Value Like TargetPattern Then
Target.Value = Replace(Target.Value, " ", "'-", , 1) & """"""
End If
Whoops:
Application.EnableEvents = True
End Sub

If you enter a number in the format you showed (with only single spaces
between the numbers), then it will be reformatted the way you want.

Rick


  #4   Report Post  
Old February 11th 08, 08:28 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2008
Posts: 1
Default Display feet inches and fraction

Yes, I mistyped the number of inch-marks... 4 of them is what I meant.

Rick


"Homer" wrote in message
...
Rick,

Thanks for your help. I input the feet then a space, inches then a space
and then the fraction. The code worked except that I would get two inch
marks at the end of the dimension. When I removed two of the six inch
(quotation) marks toward the end of the code, only one inch mark shows up.
This seems to work for what I need.

Thanks,

Homer

"Rick Rothstein (MVP - VB)" wrote:

I would like to be able to input 11 3 1/2 and have the cell display
as 11'-3 1/2" Is this possible?


Right-click the sheet tab for the sheet you want this functionality on
and
select View Code on the popup menu that appears. Copy/Paste the following
into the code window that opened up in the VBA editor...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim TargetPattern As String
TargetPattern = Target.Value
For X = 1 To Len(Target.Value)
If InStr(" /", Mid$(TargetPattern, X, 1)) = 0 Then
Mid$(TargetPattern, X, 1) = "#"
End If
Next
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Value Like TargetPattern Then
Target.Value = Replace(Target.Value, " ", "'-", , 1) & """"""
End If
Whoops:
Application.EnableEvents = True
End Sub

If you enter a number in the format you showed (with only single spaces
between the numbers), then it will be reformatted the way you want.

Rick



  #5   Report Post  
Old February 12th 08, 03:08 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 47
Default Display feet inches and fraction

So far so good. Now I need to be able to convert the dimensions to decimals
so I can add and multiply the various lengths. I found a reply from a month
a go with the subject: Subject: change feet/ inches to decimals
Where a link was given to cpearson.com which contained a formula
=LEFT(A1,FIND("'",A1)-1)+((MID(A1,FIND("'",A1)+1,LEN(A1)-FIND("'",A1)-1))/12)
when I put this formula into a cell I get 10.70833 instead of 11.29167 for
11'-3 1/2". I have been through the formula and cannot figure out what is
wrong.

"Rick Rothstein (MVP - VB)" wrote:

Yes, I mistyped the number of inch-marks... 4 of them is what I meant.

Rick


"Homer" wrote in message
...
Rick,

Thanks for your help. I input the feet then a space, inches then a space
and then the fraction. The code worked except that I would get two inch
marks at the end of the dimension. When I removed two of the six inch
(quotation) marks toward the end of the code, only one inch mark shows up.
This seems to work for what I need.

Thanks,

Homer

"Rick Rothstein (MVP - VB)" wrote:

I would like to be able to input 11 3 1/2 and have the cell display
as 11'-3 1/2" Is this possible?

Right-click the sheet tab for the sheet you want this functionality on
and
select View Code on the popup menu that appears. Copy/Paste the following
into the code window that opened up in the VBA editor...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim TargetPattern As String
TargetPattern = Target.Value
For X = 1 To Len(Target.Value)
If InStr(" /", Mid$(TargetPattern, X, 1)) = 0 Then
Mid$(TargetPattern, X, 1) = "#"
End If
Next
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Value Like TargetPattern Then
Target.Value = Replace(Target.Value, " ", "'-", , 1) & """"""
End If
Whoops:
Application.EnableEvents = True
End Sub

If you enter a number in the format you showed (with only single spaces
between the numbers), then it will be reformatted the way you want.

Rick






  #6   Report Post  
Old February 12th 08, 04:21 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 47
Default Display feet inches and fraction

When I add the following ........)-1)+((-1)*(MID...... the formula works.
It was subtracting the fraction from the whole number.


Two things I have encountered and cannot find the solution is in the VBA.
If I put text into a cell and erase the text or clear contents, an inch mark
is left. This cannot be removed unless I am in design mode.

When I try to copy one cell to another I get a popup with Run-time error
'13': Type mismatch. When I click on Debug the line that is highlighted is
TargetPattern = Target.Value.

I wonder if the two are related? Any idea what could be causing them?

Thanks,
Homer


"Homer" wrote:

So far so good. Now I need to be able to convert the dimensions to decimals
so I can add and multiply the various lengths. I found a reply from a month
a go with the subject: Subject: change feet/ inches to decimals
Where a link was given to cpearson.com which contained a formula
=LEFT(A1,FIND("'",A1)-1)+((MID(A1,FIND("'",A1)+1,LEN(A1)-FIND("'",A1)-1))/12)
when I put this formula into a cell I get 10.70833 instead of 11.29167 for
11'-3 1/2". I have been through the formula and cannot figure out what is
wrong.

"Rick Rothstein (MVP - VB)" wrote:

Yes, I mistyped the number of inch-marks... 4 of them is what I meant.

Rick


"Homer" wrote in message
...
Rick,

Thanks for your help. I input the feet then a space, inches then a space
and then the fraction. The code worked except that I would get two inch
marks at the end of the dimension. When I removed two of the six inch
(quotation) marks toward the end of the code, only one inch mark shows up.
This seems to work for what I need.

Thanks,

Homer

"Rick Rothstein (MVP - VB)" wrote:

I would like to be able to input 11 3 1/2 and have the cell display
as 11'-3 1/2" Is this possible?

Right-click the sheet tab for the sheet you want this functionality on
and
select View Code on the popup menu that appears. Copy/Paste the following
into the code window that opened up in the VBA editor...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim TargetPattern As String
TargetPattern = Target.Value
For X = 1 To Len(Target.Value)
If InStr(" /", Mid$(TargetPattern, X, 1)) = 0 Then
Mid$(TargetPattern, X, 1) = "#"
End If
Next
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Value Like TargetPattern Then
Target.Value = Replace(Target.Value, " ", "'-", , 1) & """"""
End If
Whoops:
Application.EnableEvents = True
End Sub

If you enter a number in the format you showed (with only single spaces
between the numbers), then it will be reformatted the way you want.

Rick




  #7   Report Post  
Old February 12th 08, 11:32 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2008
Posts: 1
Default Display feet inches and fraction

See inline comments...

When I add the following ........)-1)+((-1)*(MID...... the formula
works.
It was subtracting the fraction from the whole number.


I'm glad you got it sorted out.

Two things I have encountered and cannot find the solution is in the VBA.
If I put text into a cell and erase the text or clear contents, an inch
mark
is left. This cannot be removed unless I am in design mode.


Can you give us an example? Show us what is in the cell and the code you are
using to erase or clear it.

When I try to copy one cell to another I get a popup with Run-time error
'13': Type mismatch. When I click on Debug the line that is highlighted
is
TargetPattern = Target.Value.


Can you show us your code?

Rick

  #8   Report Post  
Old April 9th 10, 08:15 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2010
Posts: 1
Default Inches

Rick
I need help with something similar.I would like to enter a number as 4.75 and have it display as 4 3/4".I was able to do this in custom number format using # ??/??[$"] but spacing is off. A number like 4 15/16" look fine but 24 displays as 24 ". Any help appreciated.

E






Rick Rothstein \(MVP - VB\) wrote:

Right-click the sheet tab for the sheet you want this functionality on and
08-Feb-08

Right-click the sheet tab for the sheet you want this functionality on and
select View Code on the popup menu that appears. Copy/Paste the following
into the code window that opened up in the VBA editor...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim TargetPattern As String
TargetPattern = Target.Value
For X = 1 To Len(Target.Value)
If InStr(" /", Mid$(TargetPattern, X, 1)) = 0 Then
Mid$(TargetPattern, X, 1) = "#"
End If
Next
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Value Like TargetPattern Then
Target.Value = Replace(Target.Value, " ", "'-", , 1) & """"""
End If
Whoops:
Application.EnableEvents = True
End Sub

If you enter a number in the format you showed (with only single spaces
between the numbers), then it will be reformatted the way you want.

Rick

Previous Posts In This Thread:

On Friday, February 08, 2008 2:47 PM
Home wrote:

Display feet inches and fraction
I would like to be able to input 11 3 1/2 and have the cell display as 11'-3
1/2" Is this possible?

On Friday, February 08, 2008 3:35 PM
Rick Rothstein \(MVP - VB\) wrote:

Right-click the sheet tab for the sheet you want this functionality on and
Right-click the sheet tab for the sheet you want this functionality on and
select View Code on the popup menu that appears. Copy/Paste the following
into the code window that opened up in the VBA editor...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim TargetPattern As String
TargetPattern = Target.Value
For X = 1 To Len(Target.Value)
If InStr(" /", Mid$(TargetPattern, X, 1)) = 0 Then
Mid$(TargetPattern, X, 1) = "#"
End If
Next
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Value Like TargetPattern Then
Target.Value = Replace(Target.Value, " ", "'-", , 1) & """"""
End If
Whoops:
Application.EnableEvents = True
End Sub

If you enter a number in the format you showed (with only single spaces
between the numbers), then it will be reformatted the way you want.

Rick

On Monday, February 11, 2008 2:11 PM
Home wrote:

Rick,Thanks for your help.
Rick,

Thanks for your help. I input the feet then a space, inches then a space
and then the fraction. The code worked except that I would get two inch
marks at the end of the dimension. When I removed two of the six inch
(quotation) marks toward the end of the code, only one inch mark shows up.
This seems to work for what I need.

Thanks,

Homer

"Rick Rothstein (MVP - VB)" wrote:

On Monday, February 11, 2008 3:28 PM
Rick Rothstein \(MVP - VB\) wrote:

Yes, I mistyped the number of inch-marks... 4 of them is what I meant.
Yes, I mistyped the number of inch-marks... 4 of them is what I meant.

Rick

On Tuesday, February 12, 2008 10:08 AM
Home wrote:

So far so good.
So far so good. Now I need to be able to convert the dimensions to decimals
so I can add and multiply the various lengths. I found a reply from a month
a go with the subject: Subject: change feet/ inches to decimals
Where a link was given to cpearson.com which contained a formula
=LEFT(A1,FIND("'",A1)-1)+((MID(A1,FIND("'",A1)+1,LEN(A1)-FIND("'",A1)-1))/12)
when I put this formula into a cell I get 10.70833 instead of 11.29167 for
11'-3 1/2". I have been through the formula and cannot figure out what is
wrong.

"Rick Rothstein (MVP - VB)" wrote:

On Tuesday, February 12, 2008 11:21 AM
Home wrote:

When I add the following ........)-1)+((-1)*(MID...... the formula works.
When I add the following ........)-1)+((-1)*(MID...... the formula works.
It was subtracting the fraction from the whole number.


Two things I have encountered and cannot find the solution is in the VBA.
If I put text into a cell and erase the text or clear contents, an inch mark
is left. This cannot be removed unless I am in design mode.

When I try to copy one cell to another I get a popup with Run-time error
'13': Type mismatch. When I click on Debug the line that is highlighted is
TargetPattern = Target.Value.

I wonder if the two are related? Any idea what could be causing them?

Thanks,
Homer


"Homer" wrote:

On Tuesday, February 12, 2008 6:32 PM
Rick Rothstein \(MVP - VB\) wrote:

See inline comments...I'm glad you got it sorted out.
See inline comments...


I am glad you got it sorted out.


Can you give us an example? Show us what is in the cell and the code you are
using to erase or clear it.


Can you show us your code?

Rick

On Thursday, February 26, 2009 5:42 PM
Albert Procopio wrote:

Display feet inches and fraction
Your code works great, thanks for sharing it. How can I apply this code to a range of cells instead of an entire worksheet? For example I only want the code to effect column D, Row 6 - 15.


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk Repeating Structures Table Looping and Table Extract
http://www.eggheadcafe.com/tutorials...g-structu.aspx
  #9   Report Post  
Old April 9th 10, 08:25 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2009
Posts: 834
Default Inches

Try a format of

# ##/##

--

HTH

Bob

<E LAR wrote in message ...
Rick
I need help with something similar.I would like to enter a number as 4.75
and have it display as 4 3/4".I was able to do this in custom number
format using # ??/??[$"] but spacing is off. A number like 4 15/16" look
fine but 24 displays as 24 ". Any help appreciated.

E






Rick Rothstein \(MVP - VB\) wrote:

Right-click the sheet tab for the sheet you want this functionality on and
08-Feb-08

Right-click the sheet tab for the sheet you want this functionality on and
select View Code on the popup menu that appears. Copy/Paste the following
into the code window that opened up in the VBA editor...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim TargetPattern As String
TargetPattern = Target.Value
For X = 1 To Len(Target.Value)
If InStr(" /", Mid$(TargetPattern, X, 1)) = 0 Then
Mid$(TargetPattern, X, 1) = "#"
End If
Next
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Value Like TargetPattern Then
Target.Value = Replace(Target.Value, " ", "'-", , 1) & """"""
End If
Whoops:
Application.EnableEvents = True
End Sub

If you enter a number in the format you showed (with only single spaces
between the numbers), then it will be reformatted the way you want.

Rick

Previous Posts In This Thread:

On Friday, February 08, 2008 2:47 PM
Home wrote:

Display feet inches and fraction
I would like to be able to input 11 3 1/2 and have the cell display as
11'-3
1/2" Is this possible?

On Friday, February 08, 2008 3:35 PM
Rick Rothstein \(MVP - VB\) wrote:

Right-click the sheet tab for the sheet you want this functionality on and
Right-click the sheet tab for the sheet you want this functionality on and
select View Code on the popup menu that appears. Copy/Paste the following
into the code window that opened up in the VBA editor...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim TargetPattern As String
TargetPattern = Target.Value
For X = 1 To Len(Target.Value)
If InStr(" /", Mid$(TargetPattern, X, 1)) = 0 Then
Mid$(TargetPattern, X, 1) = "#"
End If
Next
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Value Like TargetPattern Then
Target.Value = Replace(Target.Value, " ", "'-", , 1) & """"""
End If
Whoops:
Application.EnableEvents = True
End Sub

If you enter a number in the format you showed (with only single spaces
between the numbers), then it will be reformatted the way you want.

Rick

On Monday, February 11, 2008 2:11 PM
Home wrote:

Rick,Thanks for your help.
Rick,

Thanks for your help. I input the feet then a space, inches then a space
and then the fraction. The code worked except that I would get two inch
marks at the end of the dimension. When I removed two of the six inch
(quotation) marks toward the end of the code, only one inch mark shows up.
This seems to work for what I need.

Thanks,

Homer

"Rick Rothstein (MVP - VB)" wrote:

On Monday, February 11, 2008 3:28 PM
Rick Rothstein \(MVP - VB\) wrote:

Yes, I mistyped the number of inch-marks... 4 of them is what I meant.
Yes, I mistyped the number of inch-marks... 4 of them is what I meant.

Rick

On Tuesday, February 12, 2008 10:08 AM
Home wrote:

So far so good.
So far so good. Now I need to be able to convert the dimensions to
decimals
so I can add and multiply the various lengths. I found a reply from a
month
a go with the subject: Subject: change feet/ inches to decimals
Where a link was given to cpearson.com which contained a formula
=LEFT(A1,FIND("'",A1)-1)+((MID(A1,FIND("'",A1)+1,LEN(A1)-FIND("'",A1)-1))/12)
when I put this formula into a cell I get 10.70833 instead of 11.29167
for
11'-3 1/2". I have been through the formula and cannot figure out what
is
wrong.

"Rick Rothstein (MVP - VB)" wrote:

On Tuesday, February 12, 2008 11:21 AM
Home wrote:

When I add the following ........)-1)+((-1)*(MID...... the formula
works.
When I add the following ........)-1)+((-1)*(MID...... the formula
works.
It was subtracting the fraction from the whole number.


Two things I have encountered and cannot find the solution is in the VBA.
If I put text into a cell and erase the text or clear contents, an inch
mark
is left. This cannot be removed unless I am in design mode.

When I try to copy one cell to another I get a popup with Run-time error
'13': Type mismatch. When I click on Debug the line that is highlighted
is
TargetPattern = Target.Value.

I wonder if the two are related? Any idea what could be causing them?

Thanks,
Homer


"Homer" wrote:

On Tuesday, February 12, 2008 6:32 PM
Rick Rothstein \(MVP - VB\) wrote:

See inline comments...I'm glad you got it sorted out.
See inline comments...


I am glad you got it sorted out.


Can you give us an example? Show us what is in the cell and the code you
are
using to erase or clear it.


Can you show us your code?

Rick

On Thursday, February 26, 2009 5:42 PM
Albert Procopio wrote:

Display feet inches and fraction
Your code works great, thanks for sharing it. How can I apply this code
to a range of cells instead of an entire worksheet? For example I only
want the code to effect column D, Row 6 - 15.


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk Repeating Structures Table Looping and Table Extract
http://www.eggheadcafe.com/tutorials...g-structu.aspx



  #10   Report Post  
Old February 14th 16, 04:24 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2016
Posts: 2
Default Display feet inches and fraction

On Friday, February 8, 2008 at 3:35:40 PM UTC-5, Rick Rothstein (MVP - VB) wrote:
I would like to be able to input 11 3 1/2 and have the cell display
as 11'-3 1/2" Is this possible?


Right-click the sheet tab for the sheet you want this functionality on and
select View Code on the popup menu that appears. Copy/Paste the following
into the code window that opened up in the VBA editor...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim TargetPattern As String
TargetPattern = Target.Value
For X = 1 To Len(Target.Value)
If InStr(" /", Mid$(TargetPattern, X, 1)) = 0 Then
Mid$(TargetPattern, X, 1) = "#"
End If
Next
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Value Like TargetPattern Then
Target.Value = Replace(Target.Value, " ", "'-", , 1) & """"""
End If
Whoops:
Application.EnableEvents = True
End Sub

If you enter a number in the format you showed (with only single spaces
between the numbers), then it will be reformatted the way you want.

Rick


Have you tried custom formatting and using
A1 = 157.75 inches

set A2 custom format to - # ?/??
A2 = A1
A2 should now be 157 3/4

or if you want you can build your result directly in the cell
A1 = 157.75 inches
A2=INT(A1/12)&"' "&INT(12*(A1/12-INT(A1/12))) & "-" & TEXT(INT(MOD(12*(A1/12-INT(A1/12)),1)*16),"#")&"/16"&CHAR(34)
A2 will display 13' 1-12/16"

A1 = 157.75 inches
A2 = A1 &" is equal to "&INT(A1/12)&" feet and "&INT(12*(A1/12-INT(A1/12))) & " " & TEXT(INT(MOD(12*(A1/12-INT(A1/12)),1)*16),"#")&"/16 of an inch"&CHAR(34)
A2 should now say "157.75 is equal to 13 feet and 1 12/16 of an inch"

try them all
Cheers, Patrick


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting from feet, inches and fractions to inches and decimal p Dee Setting up and Configuration of Excel 5 September 18th 07 04:18 PM
Feet, inches and fractions to inches and dec.....reposting for Pet Dee Setting up and Configuration of Excel 1 September 18th 07 04:02 PM
display feet and inches jkrager Excel Discussion (Misc queries) 2 April 21st 06 05:13 PM
is there a macro that will convert from inches to feet and inches cable guy Excel Discussion (Misc queries) 1 June 20th 05 07:29 PM
Need formulas for adding feet, inches & fraction in one cell Wayne hof Excel Worksheet Functions 1 January 7th 05 06:24 AM


All times are GMT +1. The time now is 11:50 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017