• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 153
  • Last Modified:

Change 10,000 to 100+00 in an Excel Spreadsheet

I work in Pipeline Construction and I do QC work. The way that Survey measures the line is for example, 75,880' down the line  is represented as Station 758+80. Is there a way that i can change 758+80 back to 75,880' so that i can add my joint length? I have to track the welds location, but i am only given stations for certain welds. I have all the joint lengths. I want to be able to have it so i can just make it so I can say for instance,
           current weld station # = (previous weld station #) + (previous pipe length in feet).
           current weld station # =  758+80 + 40
           current weld station # =  759+20
Then also show it back to the 758+56 format. Thank you if somebody could help me. I have something upwards of 3,000 welds to track, or i would just do it manually.
0
Sean Daly
Asked:
Sean Daly
  • 4
  • 3
  • 2
  • +1
1 Solution
 
JohnBusiness Consultant (Owner)Commented:
I did that 50 years ago (at least a consultant did) putting the + sign in the footage distance for bridgework drawings and measurements. This was a summer job when I was in University.

The answer then (in Fortran) was an assembly language routine called when printing.

My guess is you would need a macro to convert the number to text and the insert the + sign and then save in the next column.

Someone here may be able to write such a macro (not my skillset)
1
 
Sean DalyAuthor Commented:
I appreciate that Mr. John. I am basically a dummy with a computer. I know basic things, just enough to get my work done. I'm pretty good with the Bluebeam Revu program, but that's about all.
0
 
John FistereEngineer, retiredCommented:
Here's the formula for converting an integer pipe length into the pipelayer's format:

Assuming the actual length is an integer in Cell E1, put this in Cell F1 (or any cell).

= TEXT(INT(E1/100),0)&"+"&TEXT(E1-100*INT(E1/100),0)

Converting the pipelayer's format to an integer is easier. Do you need a formula for that?

John
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Sean DalyAuthor Commented:
Mr. John that worked like a charm. I plugged my joint length cell and put previous point. Without your help I don't believe i would've ever figured it out. I wouldn't mind having the other equation if you don't mind.
0
 
John FistereEngineer, retiredCommented:
If the pipefitter's string is in cell B1,Put this in C1:

=LEFT(B1,LEN(B1)-3)*100+RIGHT(B1,2)

If I follow your process correctly, the coupling pipe lengths go in column D and get added to column C to calculate column E.

Cheers,
0
 
byundtCommented:
I realize that the question has already been answered, but you may prefer one of the alternative ways shown below, which I think are simpler than the methods previously suggested. Each of my suggestions assume that you have the previous weld station in A1 and a length in B1

Using worksheet formulas

You can get the new pipe length and weld station with:
=--SUBSTITUTE(A1,"+","")            converts weld station into feet of pipe
=SUBSTITUTE(A1,"+","")+B1            returns new pipe length in feet
=TEXT(SUBSTITUTE(A1,"+","")+B1,"#0\+00")        returns new weld station
=TEXT(pipe length in feet,"#0\+00")        returns new weld station

By way of explanation, SUBSTITUTE(A1,"+","") deletes the + from your weld station, and returns the number of feet as text. But using that text in an arithmetic operation converts it into a number.

The TEXT formula then converts that value in feet back into a text value representing the weld station. To do that, it uses the odd looking number format #0\+00. In a Custom number format, you can insert an arbitrary character in the middle of a number by prefixing it with a backslash. So the odd looking number format really means to display the number as an integer, pad it on the left with zeros (if necessary) so at least three digits are displayed, and stick a + before the last two digits on the right. Using this Custom number format, you can display the following values:
Feet        Weld station
6                       0+06
60                     0+60
160                   1+60
75860           758+60

Using VBA code

If you are willing to use a little VBA, you can make your worksheet formulas seem a lot simpler. Put the code below in a regular module sheet (just like a macro) and save the file with .xlsm file extension. You can then return the new weld station using a worksheet formula like:
=NewWeldStation(A1,B1)
Function NewWeldStation(OriginalWeldStation As String, AdditionalFeet As Double, Optional StationFormat As String = "#0\+00") As String
'Adds AdditionalFeet to the length equivalent to OriginalWeldStation, then formats the result as a weld station
'If you don't specify a value for StationFormat, you get a value like 758+62 (to be interpreted as 75,862 feet). _
    If you want to show inches as well as feet, then you must supply a value for StationFormat. For example, if _
    you want to display 758+62.25 (to be interpreted as 75,862' 3") you would use the StationFormat "#0\+00.00"
Dim OriginalFeet As Double
OriginalFeet = CDbl(Replace(OriginalWeldStation, "+", ""))
NewWeldStation = Format(OriginalFeet + AdditionalFeet, StationFormat)
End Function

Open in new window


Converting stations to feet--but displaying them as stations

You can convert stations into feet with the following steps:
1. Select the cells to be converted
2. Change their number format to Custom, and use the following format #0\+00    You get to a dialog showing Custom number format by clicking "More Number Formats..." in the number format dropdown in the ribbon.
3. Use the Replace menu item on the Home ribbon, with a plus sign in the Find field and a blank (don't enter anything) in the Replace field
4. Copy a blank cell
5. Select the cells to be converted once again
6. Paste Special...Add

You can now add a pipe length to a previous station using the formula:
=A1+B1
Make sure you use the Custom number format #0\+00 in the cells containing these formulas. That way the result appears like a weld station.

With these changes, the pipe lengths will be stored as numbers rather than text. So you can add and subtract values very easily. But the numbers will still be displayed using the weld station formatting. Printouts and screen displays will look perfect to a pipe fitter, but an engineer can keep on top of the amount of pipe being used.
1
 
Sean DalyAuthor Commented:
Mr. John sorry I didn't get back to you. I used your formulas that you gave me. I got them to work for what i needed  to do. Now hopefully numbers all match.

Thank you again
0
 
John FistereEngineer, retiredCommented:
Sean,

Glad the formulas worked for you. Don't forget to identify the posts that were the the solutions.

John
0
 
Sean DalyAuthor Commented:
BYUNDT,

The information you gave me actually was a lot more helpful. Mr. John answered my question I asked, but the formulas that you showed were easier to understand. They also allowed me to skip previous unneeded steps. I appreciate both of your help. Byundt your understanding of what i'm doing, being an engineer, I think was helped. I'm sure you're used to guys like me haha. You answered the question by knowing what i needed,  not what I thought I wanted.

I tried the VBA, but it's a little advanced for me right now. When I get this job done and have more time I will try it out again.

Much appreciated,
Sean
0
 
byundtCommented:
Sean,
If you post a sample workbook in this thread, I'd be glad to show you how to use the VBA method.

Were it my workbook though, I would use the third method suggested. Keep all cell values as feet of pipe, but display them formatted as either feet of pipe or pipe stations & offset. Doing so keeps the formulas really simple.

I don't normally jump into questions that have already been answered, but this particular thread was interesting. As you surmised, I am an engineer and used that experience to divine what you really needed to accomplish. I'm glad you found the discussion helpful.

Brad
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now