Need a book? Engineering books recommendations...

Return to index: [Subject] [Thread] [Date] [Author]

Re: feet and inches in Excel

[Subject Prev][Subject Next][Thread Prev][Thread Next]
> From: "Ken Peoples" <kspeoples(--nospam--at)lvta.net>

> Do any of you know of a simple way of inputting feet and inches in =
> Excel?  I currently separate the two into two different cells but think =
> that there must be an easier way.  (I don't want to convert to decimal =
> feet or use all inches)

Oooo, I hate that, too.

Enter it as feet & inches in one cell with at least one non-numeric
separator (e.g. 10-2, 10'2", 10 2). It will be recognized as text. You
can write an equation to parse and convert to decimal value (lots of
left, right, length, value and if functions). This can all be done
within the limits of one cell if you wish.

try this equation:

=VALUE(LEFT(C7,FIND("-",C7,1)-1))+(VALUE(MID(C7,FIND("-",C7,1)+1,FIND("
",C7,1)-FIND("-",C7,1)-1))+IF(FIND("
",C7,1)>0,IF(RIGHT(C7)="0",0,VALUE(MID(C7,FIND("
",C7,1)+1,FIND("/",C7,1)-FIND("
",C7,1)-1)/VALUE(RIGHT(C7,LEN(C7)-FIND("/",C7,1))))),0))/12

Enter your text with a dash between feet-inches and a space between
inches fractions. For this equation, you MUST enter a fraction, even 0/0
but you could easily delete this if you aren't using fractions. (e.g.
999999-12 16/16) The equation looks for "-", " ", "/". The equation is
wrapping at the spaces as I look at my monitor so be careful <e.g.
FIND(" ",C7,1)>.

Note that this equation is pointing to cell C7 for the purposes of
copying to your spreadsheet only. Then you can drag/copy it around to
convert a list or any other cell.

I use this a lot for converting site coordinates or imperial/metric.

-- 
Paul Ransom, P. Eng.
Civil/Structural/Project/International
Burlington, Ontario, Canada
<mailto:ad026(--nospam--at)hwcn.org> <http://www.hwcn.org/~ad026/civil.html>

******* ****** ******* ******** ******* ******* ******* ***
*   Read list FAQ at: http://www.seaint.org/list_FAQ.asp
* 
*   This email was sent to you via Structural Engineers 
*   Association of Southern California (SEAOSC) server. To 
*   subscribe (no fee) or UnSubscribe, please go to:
*
*   http://www.seaint.org/sealist1.asp
*
*   Questions to seaint-ad(--nospam--at)seaint.org. Remember, any email you 
*   send to the list is public domain and may be re-posted 
*   without your permission. Make sure you visit our web 
*   site at: http://www.seaint.org 
******* ****** ****** ****** ******* ****** ****** ********