Insert Hyphen

**mgs** · 02-11-2013, 05:22 PM

Random data (inches) is entered into A1 of the attached spreadsheet, and the formula in A2 returns feet & inches.

How can I separate whole inches & and fractions of remaining inches (if any) with a hyphen, so that say…

• 1’ 1 11/16” becomes
• 1’ 1-11/16”

mgs

**FDibbins** · 02-11-2013, 05:27 PM

try "# - #/##

**mgs** · 02-11-2013, 05:31 PM

"Excellent - it works!"

Thanks very much.

mgs

**FDibbins** · 02-11-2013, 05:31 PM

Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

**shg** · 02-11-2013, 05:40 PM

Another way:

= TEXT(INT(A1/12), "0' ;;")
& INT(MOD(A1, 12))
& IF(MROUND(MOD(A1, 1), 1/16), TEXT(MROUND(MOD(A1, 1), 1/16), "-?/??"), "")
& """"

**mgs** · 02-11-2013, 06:26 PM

Thanks shg, yours is shorter so therefore better.
mgs

P.S. AND more accurate!
mgs

**mgs** · 02-11-2013, 06:30 PM

Thanks FDibbins, have done that.
mgs

**mgs** · 02-12-2013, 02:08 AM

Originally Posted by shg

Another way:

= TEXT(INT(A1/12), "0' ;;")
& INT(MOD(A1, 12))
& IF(MROUND(MOD(A1, 1), 1/16), TEXT(MROUND(MOD(A1, 1), 1/16), "-?/??"), "")
& """"

Hi shg,
I used your formula for a while, until Dr. Google found this one for me (it is long-winded, but it is exactly what I was originally looking for...):

=IF(A1<0,"("," ")&IF(TRUNC(ROUND(ABS(A1)*16,0)/16/12,0)<1,"",TRUNC(ROUND(ABS(A1)*16,0)/16/12,0)&"'"&IF(MOD(ABS(A1),12)=0,""," "))&IF(TRUNC(ROUND(ABS(A1)*16,0)/16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0))>=1,TRUNC(ROUND(ABS(A1)*16,0)/16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0))&IF(MOD(ABS(A1),1)=0,"","-"),"")&IF(MOD(A1,1)=0,"",ROUND(MOD(ABS(A1),1)*16,0)/GCD(ROUND(MOD(ABS(A1),1)*16,0),16)&"/"&16/GCD(ROUND(ABS(A1)*16,0),16))&IF(MOD(ABS(A1),12)=0,"","""")&IF(A1<0,")"," ")

Cheers,
mgs