Solved

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

Posted on 2016-09-24
10
88 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 96

Expert Comment

by:Experienced Member
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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
 
LVL 81

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 81

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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

632 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