|
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? |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Inches
On Friday, April 9, 2010 at 3:15:58 PM UTC-4, elar wrote:
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 Change custom number format to # ?/?? or # ??/?? |
Inches
Hello All, I know this is a very old thread, but I'm having trouble with the fact that sheets will not recognize 22' - 11 1/2" (and such) as values, but says this is text. I have tried all sorts of things and tried to make Macros work, the formulas in this feed, but I always end up with errors. Using the # ##/## does not seem to fix it either :(
What WOULD fix it, is the USA finally getting rid of this outdated nonsensical imperial system and using the decimal system, but alas, we are stuck with feet and inches. HOW do I make sheets recognize the imperial system as a VALUE instead of text? The reason I have all these numbers is because I"m exporting from an Architecture Program and I am trying to avoid having to manually type in the values again. Any thoughts would be appreciated. Thank you. On Friday, April 9, 2010 at 3:25:39 PM UTC-4, Bob Phillips wrote: 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 |
Inches
Hi DM,
Where you able to figure this one out? On Tuesday, September 17, 2019 at 11:52:25 AM UTC-4, DM wrote: Hello All, I know this is a very old thread, but I'm having trouble with the fact that sheets will not recognize 22' - 11 1/2" (and such) as values, but says this is text. I have tried all sorts of things and tried to make Macros work, the formulas in this feed, but I always end up with errors. Using the # ##/## does not seem to fix it either :( What WOULD fix it, is the USA finally getting rid of this outdated nonsensical imperial system and using the decimal system, but alas, we are stuck with feet and inches. HOW do I make sheets recognize the imperial system as a VALUE instead of text? The reason I have all these numbers is because I"m exporting from an Architecture Program and I am trying to avoid having to manually type in the values again. Any thoughts would be appreciated. Thank you. On Friday, April 9, 2010 at 3:25:39 PM UTC-4, Bob Phillips wrote: 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 |
| All times are GMT +1. The time now is 11:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com