Custom Number


J

jal111

I'm a surveyor and I always use a number format 0+00.00 for Stationing.
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.

http://www.microsoft.com/office/community/en-us/default.mspx?mid=e9af70d6-db61-4d24-8a97-1db8220257e1&dg=microsoft.public.excel.worksheet.functions
 
Ad

Advertisements

R

Rick Rothstein

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
 
J

John C

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.
 
R

Rick Rothstein

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.
 
S

Shane Devenshire

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
 
J

jal111

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 :)
 
Ad

Advertisements

R

Rick Rothstein

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).
 
J

jal111

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.)
 
R

Rick Rothstein

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.
 
R

Rick Rothstein

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 said:
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 said:
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 said:
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

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
As a matter of idle curiosity, what branch of engineering or surveying
uses that format, and why?
--
David Biddulph

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 :)

:

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 **

:

I'm a surveyor and I always use a number format 0+00.00 for
Stationing.
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.

http://www.microsoft.com/office/community/en-us/default.mspx?mid=e9af70d6-db61-4d24-8a97-1db8220257e1&dg=microsoft.public.excel.worksheet.functions
 
J

Jim Olyniec

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
 
Ad

Advertisements

R

Rick Rothstein

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 #,"+","")
 
M

MIG PER

To format cells in EXCEL for Engineering Station, Station Offset, Stationing, etc... do the following

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

I'm a surveyor and I always use a number format 0+00.00 for Stationing.
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

http://www.microsoft.com/office/community/en-us/default.mspx?mid=e9af70d6-db61-4d24-8a97-1db8220257e1&dg=microsoft.public.excel.worksheet.functions

Previous Posts In This Thread:

Custom Number
I'm a surveyor and I always use a number format 0+00.00 for Stationing.
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

http://www.microsoft.com/office/community/en-us/default.mspx?mid=e9af70d6-db61-4d24-8a97-1db8220257e1&dg=microsoft.public.excel.worksheet.functions

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 Varian
With ActiveCel
Setting = .NumberForma
.NumberFormat = "0+00.00
.NumberFormat = Settin
End Wit
End Su

--
Rick (MVP - Excel


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 *

:

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 Lon
Dim Setting As Varian
With ActiveShee
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Ro
With .Cells(LastRow + 1, "A"
Setting = .NumberForma
.NumberFormat = "0+00.00
.NumberFormat = Settin
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)



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

:

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 :)

:

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

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

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.)

:

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


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

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


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

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

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)


in message

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
 
Ad

Advertisements

M

marino.paunil

As shown below, I used: =--SUBSTITUTE(cell containing the station #,"+",""). Wow! Now I am able to add distances to stations in excel and get the next station. Very helpful. I had a starting station and need to place 400 CIDH piles 3 feet apart. I just add 3 feet to the station and the next station "magically" shows up in the next cell. Very helpful, very very helpful!!!
Thanks Rick!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top