Solved

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

Posted on 2016-09-24
10
50 Views
Last Modified: 2016-09-28
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
Comment
Question by:Sean Daly
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 90

Expert Comment

by:John Hurst
ID: 41814203
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
 

Author Comment

by:Sean Daly
ID: 41814205
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
 
LVL 2

Accepted Solution

by:
John Fistere earned 500 total points
ID: 41814232
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
 

Author Closing Comment

by:Sean Daly
ID: 41814242
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
 
LVL 2

Expert Comment

by:John Fistere
ID: 41814245
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 80

Expert Comment

by:byundt
ID: 41814296
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
 

Author Comment

by:Sean Daly
ID: 41818971
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
 
LVL 2

Expert Comment

by:John Fistere
ID: 41819094
Sean,

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

John
0
 

Author Comment

by:Sean Daly
ID: 41821059
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
 
LVL 80

Expert Comment

by:byundt
ID: 41821071
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now