Convert dimensions: mm to inches

[adfeddon]

I have a large column of numbers written like this: (90 * 120 * 85) I need to convert these mm numbers to inches in the next column over. Any help is appreciated!

[Richard Buttrey]

Hi,

=A1/25.4

[adfeddon]

It gives me an #VALUE when I put that formula in. It also tries to mulitply * the numbers since thats how they were written before ... 90*120*85. These are dimensions... I would need this to be translated to 3.54*4.72*3.35. Thanks!

[jason.b75]

Does your original data include the paranthesis?

[Richard Buttrey]

Hi,

You said you wanted to convert them to inches. I implied that you wanted the result in inches. Are there always three numbers or can they vary from row to row?. e.g. 10*20*30*40*50...etc

[adfeddon]

Yes, I didnt explain thoroughly enough before. Yes, there are always 3 numbers e.g. 90*120*85. HxWxD Thanks.

[jason.b75]

Looks ugly, but best I can come up with

=ROUND(LEFT(A2,FIND(" ",A2)-1)/25.4,2)&" * "&ROUND(TRIM(MID(SUBSTITUTE(A2,"*",REPT(" ",10)),10,10))/25.4,2)&" * "&ROUND(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A2,"*",REPT(" ",10)),20,20)),")","")/25.4,2)

[Richard Buttrey]

Another completely different approach would be to split column A into three columns using the '*' as the delimiter symbol, then join them all together again with

TEXT(A1/2.54,"0.00")&" * "&TEXT(B1/2.54,"0.00")&" * "&TEXT(C1/2.54,"0.00")

[adfeddon]

No, no parenthesis.

[daddylonglegs]

Perhaps it would be simpler to split the 3 values into 3 cells using "text to columns" functionality (with * as delimiter), then you can use a simple formula to divide each value by 25.4 and re-concatenate them.

You can use a single formula but it's a little long, i.e.

=FIXED(LEFT(A1,FIND("*",A1)-1)/25.4)&"*"&FIXED(MID(A1,FIND("*",A1)+1,FIND("*",A1,FIND("*",A1)+1)-FIND("*",A1)-1)/25.4)&"*"&FIXED(REPLACE(A1,1,FIND("*",A1,FIND("*",A1)+1),"")/25.4)

[daddylonglegs]

OK, I'm going to steal some of the best bits of jason's suggestion to shorten mine, i.e.

=FIXED(LEFT(A1,FIND("*",A1)-1)/25.4)&"*"&FIXED(MID(SUBSTITUTE(A1,"*",REPT(" ",9)),9,9)/25.4)&"*"&FIXED(REPLACE(A1,1,FIND("*",A1,FIND("*",A1)+1),"")/25.4)

any advance?

[jason.b75]

any advance?

Not without resorting to a UDF.

[daddylonglegs]

any advance?

That was simply an invitation for others to improve my suggestion (half jokingly). Jason suggests you might only be able to do that with a "UDF"

UDF = "User defined function", a function defined in VBA

[NBVC]

For some variety....

=FIXED(LEFT(A1,FIND("*",A1)-1)/25.4)&" * "&FIXED(TRIM(MID(SUBSTITUTE(A1,"*",REPT(" ",100)),101,100))/25.4)&" * "&FIXED(TRIM(MID(SUBSTITUTE(A1,"*",REPT(" ",100)),201,100))/25.4)

[adfeddon]

Both formulas work perfectly. The only difference, Jasons has spaces between the numbers and *'s. Thank you daddylonglegs and jason!!

[adfeddon]

Just curious... What do you mean by advance and UDF?

[jason.b75]

Advance was meaning improvements / changes to make the formula shorter or more efficient. As in post #10 you can sometimes look at different suggestions posted by other people, making a better formula by mixing the methods used.

A UDF is a custom function made in VBA, which you need to copy into the code editor (in excel press Alt F11, then in the editor menu choose Insert, then Module).

Option Explicit
Public Function mmtoinches(ByVal str As String)
Dim tmp, a As Long, tmpstr As String
tmp = Split(str, "*")
For a = 0 To UBound(tmp)
    tmpstr = tmpstr & Round(CDbl(tmp(a)) / 25.4, 2) & " * "
Next
mmtoinches = Left(tmpstr, Len(tmpstr) - 3)
End Function

Then instead of those long formula in the earlier replies, you simply use =mmtoinches(A2)

[adfeddon]

Ah, ok. Thanks for the explanation.

[NBVC]

and mine wasn't really an improvement, just an alternative... I was typing and posting at same time dll posted his challenge... so didn't see it by the time I posted

P.s.... although shorter isn't always most efficient either

[daddylonglegs]

P.s.... although shorter isn't always most efficient either

Damn! All these years.......

[NBVC]

obviously, it wasn't directed at you dll. Just a general comment.

[adfeddon]

I do have another problem. Not sure if I should create a new thread but here goes... Same worksheet: I converted everything to inches. Now I see one mfg has thier dimensions listed as HxWxD and the other mfg has WxHxD. Is there a way to make it so they will be in the correct order when converting to inches? Ex. Started with 90*120*70 (W*H*D) which you gave me the formula to convert to 3.54*4.72*2.76. Now, I'd like to switch the first two numbers and convert to 120*90*70 (H*W*D)and then into inches which is 4.72*3.54*2.76.

[Richard Buttrey]

Hi,

How will a formula know that the first entry is the Height?

(late edit... unless you can apply a rule like the height is always less than the width)

Unless you have an indicator somewhere and can recognise which is which, the best you can do is manually change the formula/macro for the relevant manufacturer

In the case of the text to columns approach I suggested just swap the references around. e.g.

=TEXT(B1/2.54,"0.00")&" * "&TEXT(A1/2.54,"0.00")&" * "&TEXT(C1/2.54,"0.00")

[Richard Buttrey]

Maybe you should also mark the thread as Unsolved given the addendum

[adfeddon]

Thanks again to all of you who have responded. I have everything I need for this spreadsheet and future projects like this one. Your quick response is very much appreciated!

[jason.b75]

From your last response it looks like you have a solution that you're happy with but I'll post this anyway in case it's of use.

Option Explicit
Public Function mmtoinches(ByVal str As String, Optional order As String = "whd")
Dim tmp, a As Long, tmpstr As String, tval As Double, w As Boolean, h As Boolean, d As Boolean, funcerr
tmp = Split(str, "*")
For a = 0 To 2
    Select Case LCase(Mid(order, a + 1, 1))
        Case Is = "w"
            If w Then GoTo funcerr
            tval = Round(CDbl(tmp(0)) / 25.4, 2)
            w = True
        Case Is = "h"
            If h Then GoTo funcerr
            tval = Round(CDbl(tmp(1)) / 25.4, 2)
            h = True
        Case Is = "d"
            If d Then GoTo funcerr
            tval = Round(CDbl(tmp(2)) / 25.4, 2)
            d = True
        Case Else
            GoTo funcerr
    End Select
        tmpstr = tmpstr & tval & " * "
Next
    mmtoinches = Left(tmpstr, Len(tmpstr) - 3)
    Exit Function
funcerr:
    mmtoinches = CVErr(xlErrNA)
End Function

The function is set up for a default format of w*h*d, the input value must be in this order, the output order can be defined by the optional second parameter of the function.

=mmtoinches(A2) will return the result in the default format.
=mmtoinches(A2,"hwd") will return the result in the format h*w*d, this is for example, it can be defined in any order, dwh, hdw, etc.

Failsafes included so duplicate dimensions i.e. "hhd" or unknown characters will return a #N/A! error.