Convert dimensions

[Niniel]

Hello,

I have a whole list of dimensions in mm and need to convert them to inches.
They are in one cell and look like this: 1111 x 1111 x 11. I can convert all
the numbers individually, but I am hoping to be able to save myself some time
and effort and do them as a group with Excel. Ideally, I'll be able to copy
the content of one such cell into a converter sheet that spits out the
converted numbers in 3 cells - 111 | 111 | 11.

Could somebody help me with this?
Thank you.

 

[Niniel]

Also, how can I round up to the next whole number (no decimals)? Setting the
cell format may round down, so that won't work for me in this case.

 

[tim m]

If you want to make all of this totally automated you will have to create a
macro I believe. You can do it manually with a few steps however if you want
to go that route. Here is what I did in a test.

In cell E1 I put this formula =ROUNDUP(A1*0.039,1)
I then copied that formula to cells F1 and G1
I then put some dimensions as you describe in cell A1 (I used 5000 x 5000 x
100)
I put the cursor in A1 and did 'Data'...'Text to Columns' ...
'Delimited'.... check off 'other' and put an x in the box.
When you 'Finish' it will show the dimensions rounded up in Cells E1, F1, G1

 

[James Silverton]

tim wrote on Thu, 4 Oct 2007 09:43:01 -0700:

tm> In cell E1 I put this formula =ROUNDUP(A1*0.039,1)
tm> I then copied that formula to cells F1 and G1
tm> I then put some dimensions as you describe in cell A1 (I
tm> used 5000 x 5000 x 100)
tm> I put the cursor in A1 and did 'Data'...'Text to Columns'
tm> ... 'Delimited'.... check off 'other' and put an x in the
tm> box. When you 'Finish' it will show the dimensions rounded
tm> up in Cells E1, F1, G1

tm> "Niniel" wrote:

??>> Also, how can I round up to the next whole number (no
??>> decimals)? Setting the cell format may round down, so that
??>> won't work for me in this case.

You might want to see if you can look at John Walkenbach's book,
"Excel 2002 Formulas" or later versions. He mentions a VBA
Extractelement function. I haven't used it but it looks like a
first step for you. The extraction might be combined with
formatting.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

 

[Niniel]

Hello Tim,

Thank you for your reply.
I played around with this a bit myself and ended up taking a different
approach, and it doesn't even require a macro.

I have my dimensions in A1, and then I'm extracting the numerical values
into B1, C1 and D1.
=LEFT(A1, SEARCH(" ",A1,1))
=MID(A1,SEARCH("x",A1,1)+1,SEARCH(" x",A1,SEARCH("x
",A1,1)+1)-SEARCH("x",A1,1))
=RIGHT(A1,LEN(A1)-SEARCH("x",A1,SEARCH("x",A1,1)+1))

E1, F1 and G1 then handle the conversions, and H1, I1 and J1 provide me with
the rounded up values.

May not be the most terribly efficient method, but it works.

 

[David Biddulph]

Remember that multiplying by 0.039 isn't an accurate conversion, so instead
of A1*0.039 you would be better to have A1/25.4 or CONVERT(A1,"mm","in")

 

[Niniel]

Ah, but David, that is good enough, especially since I want to round up to
the next full number afterwards. Besides, how is dividing by 25.4 more
accurate than multiplying with 0.039?
That convert function, on the other hand, that looks interesting. It's not
working for me though. Could that be because I have a formula in B1 (see
above)?

 

[James Silverton]

Niniel wrote on Thu, 4 Oct 2007 10:24:02 -0700:

N> Ah, but David, that is good enough, especially since I want
N> to round up to the next full number afterwards. Besides, how
N> is dividing by 25.4 more accurate than multiplying with
N> 0.039? That convert function, on the other hand, that looks
N> interesting. It's not working for me though. Could that be
N> because I have a formula in B1 (see above)?

Multiplying by 0.039 may well be sufficiently accurate for uour
purposes but 1 inch is *exactly* 25.4 mm and 1/0.039 is
25.64102564.........

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

 

[Niniel]

Oh, ok.
Thanks for the education.

 

[Niniel]

Looks like I don't have the right component installed:

"If this function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in."

 

[Peo Sjoblom]

If you have the CD/DVD you can just do tools>add-ins and select Analysis
ToolPak and follow the directions


--


Regards,


Peo Sjoblom

 

[Niniel]

Thank you.
Turns out, the add-in had been there all along, it just wasn't loaded.

 

[David Biddulph]

See what you get if the number of mm is 25400. Your 0.039 multiplication
would round up to 991. Do you consider that close enough to the correct
answer of 1000 ?

I think you've subsequently answered your own question about CONVERT, by
switching on the Analysis ToolPak.

 

[Niniel]

My numbers won't exceed 3000, but I see what you mean.

Interestingly enough, CONVERT doesn't come up with exactly the same numbers
as the division (identical for 3 decimals), but it does pass the 25400 test.