Calculating a distance within an interval in a cell with text

Closed
ohai309 Posts 2 Registration date Wednesday August 20, 2014 Status Member Last seen August 21, 2014 - Aug 20, 2014 at 10:56 PM
Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 - Aug 22, 2014 at 03:37 PM
I have a cell that contains words and numbers in the following format:

Dig (30'-40', 50'-65'), Fill (70'-90')

What I'd like to figure out is a formula to determine the total distance that needs to be dug in one cell and the total distance that needs to be filled in another. Do anyone know some formulas I should use? I'd like to keep the tick marks in if possible.

3 replies

Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 146
Aug 21, 2014 at 07:14 PM
ohai309, Good evening.

Sorry, but it's a little confused to understand.

Please, could you give us some examples about the data you expect to receive as result?
0
ohai309 Posts 2 Registration date Wednesday August 20, 2014 Status Member Last seen August 21, 2014
Aug 21, 2014 at 10:22 PM
Hi Mazzaroi,

I have a spreadsheet containing data for sanitary sewers. For each sewer, I have a cell that gives a recommendation for repairing that segment. I want to automate my spreadsheet such that I can type in a rehabilitation measure and another cell will spit out the quantity that needs to be repaired (so I can multiply it by an already determined unit price). For example, my recommendation might be the following:

Case 1: One recommendation, one interval

Grout (5'-21')

Meaning I want to put grout starting at 5' in the sewer and ending at 21' in the sewer. If I can pull out the quantity to grout from that format, I'll be good. The result for this case would be 16.

Case 2: One recommendation, multiple intervals

Grout (5'-21', 68'-102')

Meaning I want to grout over two separate intervals. I'd want to pull out the total footage to grout for that segment, making the result be (21-5)+(102-68)= 50

Case 3: Two recommendations, one interval each

Grout (5'-21'); Line (30'-40')

Meaning I want to grout for one interval and put the quantity to grout in one cell, and line over another interval and put that quantity in another cell. The cell in my "Grout" column would say 16 and the cell in my "Line" column would say 10.

And so on and so forth with various combinations with those three cases.
0
Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 146
Aug 22, 2014 at 03:37 PM
ohai309, Good afternoon.

I don't know why you need to use one cell to describe a complex Text-Number string as your technical recommendation. Maybe you have no other choice.

You could use some separate columns to create your recommendation.
Then the excel formulas would work faster and in easier way.

If, for some reason, after using some separated columns for your calculus, you need to have this unique Text-Number cell, maybe in a report, is possible to using a formula to form it.

"...And so on and so forth with various combinations with those three cases...."
I think that for this kind of situation, without fixed standards, you'll need a VBA Programming.
It's always more flexible.
0