Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.setup
DEE DEE is offline
external usenet poster
 
Posts: 250
Default Converting from feet, inches and fractions to inches and decimal p

Morning,

We have a program that exports a table from Auto-cad to Excel, using a
program called TableBuilder. It exports using feet, inches and fractions eg
6'-5 1/2" or 4'-0".

I'm looking for a way to convert this to inches and decimal points and all
the -, ', " removed.
for example 6'-5 1/2" will be 77.5 and 4'-0" will be 48

Any ideas? It's a huge spreadsheet and doing each conversion individually
will drive me nuts.

Cheers in advance

Dee
  #2   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 8,856
Default Converting from feet, inches and fractions to inches and decimal p

It will be a complex formula (almost finished), but to avoid problems
with converting cell references can you tell me which is the first
cell that this will apply to in your sheet, eg F2, and which column
you want the formula to go into? Then I can give you the exact formula
which you will be able to copy/paste into your sheet.

Pete

On Sep 17, 10:06 pm, Dee wrote:
Morning,

We have a program that exports a table from Auto-cad to Excel, using a
program called TableBuilder. It exports using feet, inches and fractions eg
6'-5 1/2" or 4'-0".

I'm looking for a way to convert this to inches and decimal points and all
the -, ', " removed.
for example 6'-5 1/2" will be 77.5 and 4'-0" will be 48

Any ideas? It's a huge spreadsheet and doing each conversion individually
will drive me nuts.

Cheers in advance

Dee



  #3   Report Post  
Posted to microsoft.public.excel.setup
DEE DEE is offline
external usenet poster
 
Posts: 250
Default Converting from feet, inches and fractions to inches and decim

That would be amazing! The first cell this needs to applied to is D5 and the
same column D.

Thank you in advance

Dee

"Pete_UK" wrote:

It will be a complex formula (almost finished), but to avoid problems
with converting cell references can you tell me which is the first
cell that this will apply to in your sheet, eg F2, and which column
you want the formula to go into? Then I can give you the exact formula
which you will be able to copy/paste into your sheet.

Pete

On Sep 17, 10:06 pm, Dee wrote:
Morning,

We have a program that exports a table from Auto-cad to Excel, using a
program called TableBuilder. It exports using feet, inches and fractions eg
6'-5 1/2" or 4'-0".

I'm looking for a way to convert this to inches and decimal points and all
the -, ', " removed.
for example 6'-5 1/2" will be 77.5 and 4'-0" will be 48

Any ideas? It's a huge spreadsheet and doing each conversion individually
will drive me nuts.

Cheers in advance

Dee




  #4   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 8,856
Default Converting from feet, inches and fractions to inches and decimal p

Hi Dee,

I can see on Google Groups that you have replied, but I can't read
your reply,and I couldn't find it at all on the microsoft discussions
site - any chance you can post it again to see if I can read that one?

Pete

On Sep 18, 10:35 am, Pete_UK wrote:
It will be a complex formula (almost finished), but to avoid problems
with converting cell references can you tell me which is the first
cell that this will apply to in your sheet, eg F2, and which column
you want the formula to go into? Then I can give you the exact formula
which you will be able to copy/paste into your sheet.

Pete

On Sep 17, 10:06 pm, Dee wrote:



Morning,


We have a program that exports a table from Auto-cad to Excel, using a
program called TableBuilder. It exports using feet, inches and fractions eg
6'-5 1/2" or 4'-0".


I'm looking for a way to convert this to inches and decimal points and all
the -, ', " removed.
for example 6'-5 1/2" will be 77.5 and 4'-0" will be 48


Any ideas? It's a huge spreadsheet and doing each conversion individually
will drive me nuts.


Cheers in advance


Dee- Hide quoted text -


- Show quoted text -



  #5   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 8,856
Default Converting from feet, inches and fractions to inches and decimal p

It's alright - I can see now that your first cell is D5. You can't put
the formula in the same column as it will overwrite the data that you
have, so make use of an empty column (eg column H) and put this
formula in H5:

=IF(ISNUMBER(FIND("
",D5)),LEFT(D5,FIND("'",D5)-1)*12+MID(D5,FIND("-",D5)+1,2)+MID(D5,FIND("
",D5),FIND("/",D5)-FIND(" ",D5))/MID(SUBSTITUTE(D5,CHAR(34),"
"),FIND("/",D5)+1,3),LEFT(D5,FIND("'",D5)-1)*12+MID(SUBSTITUTE(D5,CHAR(34),"
"),FIND("-",D5)+1,2))

This is all one formula, so be wary of spurious line-breaks that the
newsgroups sometimes introduce (usually showing up as hyphens).

I've tested it out on your examples and also on 14'-11 23/64", which
returns the correct result of 179.359375, so it seems to work. Format
the cell with the appropriate number of decimal places, and then copy
the formula down.

If you don't want this extra column in your sheet, you can fix the
values from the formula and then paste them over the original values
in column D and then delete column H.

Hope this helps.

Pete



On Sep 18, 2:01 pm, Pete_UK wrote:
Hi Dee,

I can see on Google Groups that you have replied, but I can't read
your reply,and I couldn't find it at all on the microsoft discussions
site - any chance you can post it again to see if I can read that one?

Pete

On Sep 18, 10:35 am, Pete_UK wrote:



It will be a complex formula (almost finished), but to avoid problems
with converting cell references can you tell me which is the first
cell that this will apply to in your sheet, eg F2, and which column
you want the formula to go into? Then I can give you the exact formula
which you will be able to copy/paste into your sheet.


Pete


On Sep 17, 10:06 pm, Dee wrote:


Morning,


We have a program that exports a table from Auto-cad to Excel, using a
program called TableBuilder. It exports using feet, inches and fractions eg
6'-5 1/2" or 4'-0".


I'm looking for a way to convert this to inches and decimal points and all
the -, ', " removed.
for example 6'-5 1/2" will be 77.5 and 4'-0" will be 48


Any ideas? It's a huge spreadsheet and doing each conversion individually
will drive me nuts.


Cheers in advance


Dee- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -





  #6   Report Post  
Posted to microsoft.public.excel.setup
DEE DEE is offline
external usenet poster
 
Posts: 250
Default Converting from feet, inches and fractions to inches and decim

Hey Pete,

Thank you again. I'll give it a go later today, when I'm in work.
Regs

Dee

"Pete_UK" wrote:

It's alright - I can see now that your first cell is D5. You can't put
the formula in the same column as it will overwrite the data that you
have, so make use of an empty column (eg column H) and put this
formula in H5:

=IF(ISNUMBER(FIND("
",D5)),LEFT(D5,FIND("'",D5)-1)*12+MID(D5,FIND("-",D5)+1,2)+MID(D5,FIND("
",D5),FIND("/",D5)-FIND(" ",D5))/MID(SUBSTITUTE(D5,CHAR(34),"
"),FIND("/",D5)+1,3),LEFT(D5,FIND("'",D5)-1)*12+MID(SUBSTITUTE(D5,CHAR(34),"
"),FIND("-",D5)+1,2))

This is all one formula, so be wary of spurious line-breaks that the
newsgroups sometimes introduce (usually showing up as hyphens).

I've tested it out on your examples and also on 14'-11 23/64", which
returns the correct result of 179.359375, so it seems to work. Format
the cell with the appropriate number of decimal places, and then copy
the formula down.

If you don't want this extra column in your sheet, you can fix the
values from the formula and then paste them over the original values
in column D and then delete column H.

Hope this helps.

Pete



On Sep 18, 2:01 pm, Pete_UK wrote:
Hi Dee,

I can see on Google Groups that you have replied, but I can't read
your reply,and I couldn't find it at all on the microsoft discussions
site - any chance you can post it again to see if I can read that one?

Pete

On Sep 18, 10:35 am, Pete_UK wrote:



It will be a complex formula (almost finished), but to avoid problems
with converting cell references can you tell me which is the first
cell that this will apply to in your sheet, eg F2, and which column
you want the formula to go into? Then I can give you the exact formula
which you will be able to copy/paste into your sheet.


Pete


On Sep 17, 10:06 pm, Dee wrote:


Morning,


We have a program that exports a table from Auto-cad to Excel, using a
program called TableBuilder. It exports using feet, inches and fractions eg
6'-5 1/2" or 4'-0".


I'm looking for a way to convert this to inches and decimal points and all
the -, ', " removed.
for example 6'-5 1/2" will be 77.5 and 4'-0" will be 48


Any ideas? It's a huge spreadsheet and doing each conversion individually
will drive me nuts.


Cheers in advance


Dee- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I convert decimal to feet and inches Erik Excel Worksheet Functions 5 October 25th 07 11:54 PM
Fractions, Feet, Inches, & macros [email protected] Excel Discussion (Misc queries) 7 June 21st 07 05:37 PM
Convert feet/inches to decimal dingy101 Excel Worksheet Functions 16 February 21st 06 11:18 PM
How do i convert feet and inches to a decimal value? go_chrisg Excel Worksheet Functions 4 May 22nd 05 04:33 PM
Converting inches to feet & inches. svech61 Excel Worksheet Functions 2 November 27th 04 05:23 AM


All times are GMT +1. The time now is 12:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"