How to convert 6'-3 3/8" to usable numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MarvInBoise - 11 Mar 2008 14:58 GMT

I have a large spredsheet of data that comes in like 6'-3 3/8" that I need to
split out so I have the 6 (feet) in one column and the fractional inches (3
3/8) converted in the next column to 3.375, etc. Below is some sample data:

4'-1 3/8" to 4 | 1.375
9'-2 1/2" to 9 | 2.500
7'-3 1/8" to 7 | 3.125

Any help is greatly appreciated as these are weekly data conversion needs.

Thanks!

Signature

Marv Lusk

Reply to this Message

Martin Fishlock - 11 Mar 2008 15:23 GMT

Hi Marv:

I split the task up into four seperate parts as it is easier to handle:

B5=4'-1 3/8"
C5=LEFT(B5,FIND("'",B5,1)-1) => 4
D5=MID(B5,FIND("'",B5,1)+2,FIND(" ",B5,1)-FIND("'",B5,1)-2) => 1
E5=MID(B5,FIND(" ",B5,1)+1,FIND("/",B5,1)-FIND(" ",B5,1)-1) =>3
F5=MID(B5,FIND("/",B5,1)+1,FIND("""",B5,1)-FIND("/",B5,1)-1) =>8

This is not a perfect solution as it does not deal with the problems of
errors in the data and the numbers with no inches nor factions of inches.

But it will get you going.

I would personally put it in macro, it is a lot easier to manage.

Signature

Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

> I have a large spredsheet of data that comes in like 6'-3 3/8" that I need to
> split out so I have the 6 (feet) in one column and the fractional inches (3
[quoted text clipped - 7 lines]
>
> Thanks!

Reply to this Message

BoniM - 11 Mar 2008 16:10 GMT

If 6"-3 3/8" is in cell A2

=VALUE(LEFT(A2,FIND("'",A2)-1))

will give the number of feet

=MID(A2,FIND("-",A2)+1,FIND(" ",A2)-FIND("-",A2)-1)+MID(A2,FIND("
",A2)+1,FIND("/",A2)-FIND("
",A2)-1)/MID(A2,FIND("/",A2)+1,LEN(A2)-FIND("/",A2)-1)

will give the number of inches converted to decimal format.

Reply to this Message

BoniM - 11 Mar 2008 16:17 GMT

Just to make sure the spacing is correct for final formula:

=MID(A2,FIND("-",A2)+1,FIND(" ",A2)-FIND("-",A2)-1)+
MID(A2,FIND(" ",A2)+1,FIND("/",A2)-
FIND(" ",A2)-1)/MID(A2,FIND("/",A2)+1,LEN(A2)-FIND("/",A2)-1)

Reply to this Message

MarvInBoise - 11 Mar 2008 16:18 GMT

Thank you for the fast and expert feedback; we'll give it a shot!

Signature

Marv Lusk

> If 6"-3 3/8" is in cell A2
>
[quoted text clipped - 19 lines]
> >
> > Thanks!

Reply to this Message

Ron Rosenfeld - 11 Mar 2008 18:07 GMT

>I have a large spredsheet of data that comes in like 6'-3 3/8" that I need to
>split out so I have the 6 (feet) in one column and the fractional inches (3
[quoted text clipped - 7 lines]
>
>Thanks!

Feet:

=--LEFT(A1,FIND("'",A1)-1)

Inches:

=--MID(A1,FIND("-",A1)+1,FIND("""",A1)-FIND("-",A1)-1)

--ron

Reply to this Message