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

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)

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.

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.

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,

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

Select allOpen 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.

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

Sean,

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

John

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

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