Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

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.

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.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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

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

John

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial=LEFT(B1,LEN(B1)-3)*100+RI

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,

=--SUBSTITUTE(A1,"+","") converts weld station into feet of pipe

=SUBSTITUTE(A1,"+","")+B1 returns new pipe length in feet

=TEXT(SUBSTITUTE(A1,"+",""

=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

=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
```

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.

Thank you again

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

John

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

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

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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)