Subtracting feet and inches while maintaining format

[echeverriajii]

Hello everyone, new here. I joined cause I have a question. How do I subtract from column H? I am trying to subtract just 6 inches from the value in column H repeatedly and keeping it in the same format?


PUMP FOUNDATION FDN.JPG

[Pepe Le Mokko]

Any chance of posting an xlsx file instead of xls?

[echeverriajii]

I just added it.

[MrShorty]

What flexibility do you have and what is absolutely unchangeable? The real challenge with something like this is that "80'-5 7/8"" is a text string that has no numeric meaning to Excel. So the bulk of the work is parsing/manipulating the text string to get a meaningful number, perform the subtraction (the easy part), then parse the result back into text format.

If you have the flexibility, here's how I would set something like this up.

1) For the calculation part, I don't want any text strings, so I would enter these numbers in a way that they are meaningful numbers. Decimal inches might be easiest. Picking a random column to store the numbers (K), enter =80*12+6 into K3, same in K4, =80*12+5 7/8 in K5 and so on. It's a bit of a tedious way to enter the data, but should be no less tedious than entering the text strings currently in column H.
2) Perform the subtraction in column L. =K3-6 copied down.
3) Column H can convert the number in K into the desired text string. =FLOOR(K3,12)/12&"'-"&TEXT(MOD(K3,12),"# ##/##")&CHAR(34). Copy down as far as need. Copy into column I to get the result of subtraction.

Use a similar strategy for any other "numbers" in the table that you need to perform calculations on. I don't know if you will like this approach, but something like that is how I would do this.

[echeverriajii]

Well, thank you for your help MrShorty. I am doing data extractions from autocad and the text string format is what the company uses. So every column you see was populated by autocad data extraction. This is just a sample schedule I created to test my idea. Most of their schedules are over 100 foundations and hard to keep track of. Right now they type everything by hand and believe me they make tons of mistakes. What I am trying to do take what is in column H Top of concrete is 80'-6" and I just need to show top of grout 80'-0". I want to make it to where nothing has to be hand typed to avoid the mistakes they make now. Let me see if I can use the information you provided and hopefully turn the text string into decimal without typing any of it.

[MrShorty]

I know almost nothing about AutoCad, but I would expect that it should be possible to include a "decimal feet" or "decimal inches" column in what AutoCad outputs. Would you be allowed to go back to AutoCad and modify the output so you get a decimal inch or decimal feet column?

[echeverriajii]

I was thinking about doing just that. If I were to extract it in decimal format and then take your string you sent earlier, I wonder if that would change it to architectural units. Let me try it.

[echeverriajii]

It worked. Thank you sir.

[echeverriajii]

Just one question, how would you limit the denominator down to a 1/16? Right now the denominator is 1/57. Here is the string I used. =FLOOR(I3,12)/12&"'-"&TEXT(MOD(I3,12),"# ##/##")&CHAR(34)

[MrShorty]

I would do something to round I3 to the nearest 16th. If Autocad cannot output values to the nearest 16th, then use Excel's MROUND() function somewhere. =MROUND(I3,1/16) in a helper column, or nested inside of the previous function =FLOOR(MROUND(I3,1/16),12)/12....

[echeverriajii]

I limited the decimal to two decimal places in autocad. I am going to try MROUND. Is it possible to add that in to this =FLOOR(I3,12)/12&"'-"&TEXT(MOD(I3,12),"# ##/##")&CHAR(34)? By the way, what does "&CHAR(34) do? I am trying to understand all of it. I appreciate all your help.

[MrShorty]

As I tried to show, replace all of the instances of I3 with MROUND(I3,1/16). My previous post shows the FLOOR() part, the MOD part would be ...MOD(MROUND(I3,1/16),12)...

Because Excel uses the quotation mark character to indicate string literals within a formula, it can be a challenge to include a quotation mark character as part of a formula. 34 is the ASCII character code for the quotation mark character, so I used the CHAR() function rather than try to figure out the correct nesting of quotation marks to get a single quotation mark character in the output.