Custom Number
jal111
Everytime I need to use that format, I always have to create it. It will be
nice if the 0+00.00 format will be included in your list of Custom formats
for numbers. It will benefit all the Surveyors and Engineers users.
Thanks
----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
Rick Rothstein
ThisWorkbook module, the format you want should automatically be available
in any workbook you open...
Private Sub Workbook_Open()
Dim Setting As Variant
With ActiveCell
Setting = .NumberFormat
.NumberFormat = "0+00.00"
.NumberFormat = Setting
End With
End Sub
--
Rick (MVP - Excel)
"jal111" <jal...@discussions.microsoft.com> wrote in message
news:E9AF70D6-DB61-4D24...@microsoft.com...
John C
The issue with trying to make everyone happy is that it is impossible. Just
think, say MS decided to make 1000 different groups happy by adding their
format to the current list of formats, would you want to scroll through 1000
different number formats to find your one?
You might also look into making your own template.
--
** John C **
Rick Rothstein
does not always work. Try putting this in a Module in your PERSONAL.XLS
workbook instead...
Sub SurveyFormat()
Dim LastRow As Long
Dim Setting As Variant
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Cells(LastRow + 1, "A")
Setting = .NumberFormat
.NumberFormat = "0+00.00"
.NumberFormat = Setting
End With
End With
End Sub
Then, simply Run this macro (Alt+F8) whenever you want the 0+00.00 format
available in the Custom Format listing.
--
Rick (MVP - Excel)
"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:e8l5hp1S...@TK2MSFTNGP04.phx.gbl...
Shane Devenshire
You can handle this problem another way:
1. You can open a blank workbook and add the Custom format
2. Then choose File, Save and change the name to Book
3. Change the Files of Type to template (*.xlt)
4. Change the location to the \XLStart folder (usually located in
C:\Program Files\Microsoft Office\Office11\XLStart
This will handle all new workbooks. Every new workbook that opens will have
the built-in custom format. Everytime you start Excel the workbook that
opens will be this one. Evertime you click the New button this will be the
file that is used.
If this helps, please click the Yes button.
cheers,
Shane Devenshire
jal111
imagine just here in the US, how many Engineering and Surveying companies
that will benefit if we add 0+00.00 format to our custom number. What more if
we are talking worldwide. For the meantime, I very satisfied with Rick and
Shane's suggestion. Both of them works. Thanks Guys :)
David Biddulph
that format, and why?
--
David Biddulph
"jal111" <jal...@discussions.microsoft.com> wrote in message
news:8D37420E-A21B-4C0A...@microsoft.com...
Rick Rothstein
it is how the 100-foot stations on a center and/or survey line are marked
off (stations are labeled, as an example, 24, and measurements from that
100-foot station, say 12.34 feet down from it, are labeled 24+12.34).
--
Rick (MVP - Excel)
"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
news:-I6dndn3oaQJhLbU...@bt.com...
jal111
Engineering, especially in Civil,Highway and Traffic. I can say Electrical
and Mechanical too, sometimes they deal with lines(pipelines, trenches etc.)
David Biddulph
recording two different numbers, hence it doesn't make sense as a format for
a cell containing one number in Excel. If recording two different
quantities, it wants two columns.
--
David Biddulph
"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:e0UjK9nT...@TK2MSFTNGP03.phx.gbl...
Rick Rothstein
2400 feet from Station 0, so Station 24+12.34 is really 2412.34 feet from
Station 0. The + notation is a convenience as it allows you to look at a
station and immediately see the whole station it is measured from (24) and
how far past it it is 12.34 feet... this this easier to see with the +
notation than looking at 2412.34 directly.
--
Rick (MVP - Excel)
"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
news:VaqdndskVPQRsbbU...@bt.com...
David Biddulph
the 24 was measuring along the line and the 12.34 at right angles to the
line. That is presumably not the case, from what you are now saying.
--
David Biddulph
"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:ugSZvroT...@TK2MSFTNGP05.phx.gbl...
Rick Rothstein
right or left (looking up station, that is, looking in the direction of
increasing stations), from a specified station on the center and/or survey
line... 24+12.34 is an example of such a station.
--
Rick (MVP - Excel)
"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
news:s9GdnQtEbv2lJbbU...@bt.com...
jimolyniec
Left(cell containing the station #,1)+Right(cell containing the station #,2).
The only oddity is that when the stations start with 2 digits, the formula needs to have the "1" changed to "2" and when it has 3 digits, it will have to be "3".
Jim
Rick Rothstein
part to the left of the + sign and add it to the part on the right of the +
sign, this formula is much easier and it accomplishes the same end result...
=--SUBSTITUTE(cell containing the station #,"+","")
--
Rick (MVP - Excel)
"Jim Olyniec" wrote in message news:2008121421...@scottsboro.org...
MIG PER
Select the cell, cells, row or column or sheet that you would like to format.
Then right click and select the following commands:
FORMAT CELL / NUMBER / CUSTOM
Then under "TYPE"
Enter any of the following:
##+##
###+##
###+##.###
Each pound sign will represent a digit and everything to the right of the decimal point will represent a decimal place.
(You can get creative in your formats or edit the ones Excel provides by simply clicking on them and then editing their format.)
Click OK and your done.
jal11 wrote:
Custom Number
20-Nov-08
Thanks
Previous Posts In This Thread:
On Thursday, November 20, 2008 3:33 PM
jal11 wrote:
Custom Number
Thanks
On Thursday, November 20, 2008 4:53 PM
Rick Rothstein wrote:
If you put the following Workbook_Open in your PERSONAL.
If you put the following Workbook_Open in your PERSONAL.XLS workbook's
ThisWorkbook module, the format you want should automatically be available
in any workbook you open...
Private Sub Workbook_Open()
Dim Setting As Variant
With ActiveCell
Setting = .NumberFormat
.NumberFormat = "0+00.00"
.NumberFormat = Setting
End With
End Sub
--
Rick (MVP - Excel)
"jal111" <jal...@discussions.microsoft.com> wrote in message
news:E9AF70D6-DB61-4D24...@microsoft.com...
On Thursday, November 20, 2008 5:18 PM
john wrote:
See Rick's suggestion as to how to get your format as needed.
See Rick's suggestion as to how to get your format as needed.
The issue with trying to make everyone happy is that it is impossible. Just
think, say MS decided to make 1000 different groups happy by adding their
format to the current list of formats, would you want to scroll through 1000
different number formats to find your one?
You might also look into making your own template.
--
** John C **
"jal111" wrote:
On Thursday, November 20, 2008 5:21 PM
Rick Rothstein wrote:
It appears that using the PERSONAL.
It appears that using the PERSONAL.XLS workbook's Workbook_Open procedure
does not always work. Try putting this in a Module in your PERSONAL.XLS
workbook instead...
Sub SurveyFormat()
Dim LastRow As Long
Dim Setting As Variant
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Cells(LastRow + 1, "A")
Setting = .NumberFormat
.NumberFormat = "0+00.00"
.NumberFormat = Setting
End With
End With
End Sub
Then, simply Run this macro (Alt+F8) whenever you want the 0+00.00 format
available in the Custom Format listing.
--
Rick (MVP - Excel)
"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:e8l5hp1S...@TK2MSFTNGP04.phx.gbl...
On Thursday, November 20, 2008 11:48 PM
ShaneDevenshir wrote:
RE: Custom Number
Hi,
You can handle this problem another way:
1. You can open a blank workbook and add the Custom format
2. Then choose File, Save and change the name to Book
3. Change the Files of Type to template (*.xlt)
4. Change the location to the \XLStart folder (usually located in
C:\Program Files\Microsoft Office\Office11\XLStart
This will handle all new workbooks. Every new workbook that opens will have
the built-in custom format. Everytime you start Excel the workbook that
opens will be this one. Evertime you click the New button this will be the
file that is used.
If this helps, please click the Yes button.
cheers,
Shane Devenshire
"jal111" wrote:
On Monday, November 24, 2008 1:50 PM
jal11 wrote:
I don't considered us just part of a user group you're talking about.
I don't considered us just part of a user group you're talking about. Can you
imagine just here in the US, how many Engineering and Surveying companies
that will benefit if we add 0+00.00 format to our custom number. What more if
we are talking worldwide. For the meantime, I very satisfied with Rick and
Shane's suggestion. Both of them works. Thanks Guys :)
"John C" wrote:
On Monday, November 24, 2008 4:15 PM
David Biddulph wrote:
As a matter of idle curiosity, what branch of engineering or surveying uses
As a matter of idle curiosity, what branch of engineering or surveying uses
that format, and why?
--
David Biddulph
On Monday, November 24, 2008 4:54 PM
Rick Rothstein wrote:
Road design (I was a road designer for more than 30 years) and surveying...
Road design (I was a road designer for more than 30 years) and surveying...
it is how the 100-foot stations on a center and/or survey line are marked
off (stations are labeled, as an example, 24, and measurements from that
100-foot station, say 12.34 feet down from it, are labeled 24+12.34).
--
Rick (MVP - Excel)
"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
news:-I6dndn3oaQJhLbU...@bt.com...
On Monday, November 24, 2008 5:20 PM
jal11 wrote:
nI can say all branches of Surveying use it and almost all of the branches of
nI can say all branches of Surveying use it and almost all of the branches of
Engineering, especially in Civil,Highway and Traffic. I can say Electrical
and Mechanical too, sometimes they deal with lines(pipelines, trenches etc.)
"Rick Rothstein" wrote:
On Monday, November 24, 2008 5:36 PM
David Biddulph wrote:
Ah, that rings vague bells from my dim and distant past.
Ah, that rings vague bells from my dim and distant past. So that format is
recording two different numbers, hence it doesn't make sense as a format for
a cell containing one number in Excel. If recording two different
quantities, it wants two columns.
--
David Biddulph
"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:e0UjK9nT...@TK2MSFTNGP03.phx.gbl...
On Monday, November 24, 2008 6:18 PM
Rick Rothstein wrote:
Not really.
Not really. Since the station are 100 feet apart, Station 24 is actually
2400 feet from Station 0, so Station 24+12.34 is really 2412.34 feet from
Station 0. The + notation is a convenience as it allows you to look at a
station and immediately see the whole station it is measured from (24) and
how far past it it is 12.34 feet... this this easier to see with the +
notation than looking at 2412.34 directly.
--
Rick (MVP - Excel)
"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
news:VaqdndskVPQRsbbU...@bt.com...
On Tuesday, November 25, 2008 3:32 AM
David Biddulph wrote:
When you said that it was "12.
When you said that it was "12.34 feet down from" the station, I assumed that
the 24 was measuring along the line and the 12.34 at right angles to the
line. That is presumably not the case, from what you are now saying.
--
David Biddulph
"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:ugSZvroT...@TK2MSFTNGP05.phx.gbl...
On Tuesday, November 25, 2008 9:06 AM
Rick Rothstein wrote:
No, the number after the plus is not an offset...
No, the number after the plus is not an offset... the offsets are measured,
right or left (looking up station, that is, looking in the direction of
increasing stations), from a specified station on the center and/or survey
line... 24+12.34 is an example of such a station.
--
Rick (MVP - Excel)
"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
news:s9GdnQtEbv2lJbbU...@bt.com...
On Sunday, December 14, 2008 9:50 PM
Jim Olyniec wrote:
Survey stations in excel
Here is what I did in Excel. I entered the station # in one of my columns. Then in the next column I converted it to a number for additional calculations I did. The conversation uses the text formula function to select the full station to the left of the "+" and multiply it by 100 and then add the 2 far right numbers.
Left(cell containing the station #,1)+Right(cell containing the station #,2).
The only oddity is that when the stations start with 2 digits, the formula needs to have the "1" changed to "2" and when it has 3 digits, it will have to be "3".
Jim
On Sunday, December 14, 2008 10:11 PM
Rick Rothstein wrote:
While you could use FIND to find the + sign and concatenate 100 times the part
While you could use FIND to find the + sign and concatenate 100 times the
part to the left of the + sign and add it to the part on the right of the +
sign, this formula is much easier and it accomplishes the same end result...
=--SUBSTITUTE(cell containing the station #,"+","")
--
Rick (MVP - Excel)
"Jim Olyniec" wrote in message news:2008121421...@scottsboro.org...
Submitted via EggHeadCafe - Software Developer Portal of Choice
WCF Data Services / WCF Behaviors And Server Side Processing
http://www.eggheadcafe.com/tutorials/aspnet/7597ebc9-868a-420b-96d0-119d3a501d60/wcf-data-services--wcf-behaviors-and-server-side-processing.aspx
marino...@gmail.com
Thanks Rick!
hashi...@gmail.com
Structural Engineer here...KISS method.
You could just go to the cell, click the Numbers Format, go to More Numbers Format, go down to Custom and enter this in the Type: dialogue
#+00.00
it takes the digits below the 100's place and put it after the + sign. what ever is in the 100's and above goes before it. Or go big and add a designator for feet :-)
#+00.00 "ft"
awalley...@gmail.com
This will allow you to use mathematical functions and display stationing in typical fashion
