Dimension (volume) conversion from mm to in

[rodmanlab]

Hi,

I have several hundreds of product size / dimensions that are in 1 column under the following format:

Height x Length x Width .... all in mm and need to convert to inches ... but that's not all:
- Some cells are mixed, so instead of dimensions being in a single order such as HxLxW they can be a different order, like LxWxH or HxWxL, etc.
- Some are with the following format: "211mm x 456mm x 422mm"

This is a single problem but can see two separate issues here.

I've tried a two-dimension solution:
=LOOKUP(99^99,--LEFT(A1,ROW($A$1:$A$10)))&"x"&25.4*LOOKUP(99^99,--RIGHT(A1,ROW($A$1:$A$10)))/25.4

which works well grabbing the "H" and the "W" of my original example, but cannot grab the 3rd dimension ...

This initially seems a bit of a trivial issue but now I'm out of ideas and could truly use some help.

Thanks,

Rod

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[vandan_tanna]

may be

= ROUND(LEFT(SUBSTITUTE(SUBSTITUTE(A2,"mm",""),"x",REPT(" ",100)),100)/25.4,2) & " x " & ROUND(MID(SUBSTITUTE(SUBSTITUTE(A2,"mm",""),"x",REPT(" ",100)),101,100)/25.4,2) & " x " & ROUND(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,"mm",""),"x",REPT(" ",100)),100)/25.4,2)

and copy down

[rodmanlab]

Here is the example file, as you can see, there's a big mix of formats ... the intention then is on Column C to automatically convert to inches and maybe clean format too.

[vandan_tanna]

very similar to my #3 above except it handles x, X and *

=ROUND(LEFT(SUBSTITUTE(UPPER(SUBSTITUTE(A2,"mm","")),"X",REPT(" ",100)),100)/25.4,2) & " X " & ROUND(MID(SUBSTITUTE(UPPER(SUBSTITUTE(A2,"mm","")),"X",REPT(" ",100)),101,100)/25.4,2) & " x " & ROUND(RIGHT(SUBSTITUTE(UPPER(SUBSTITUTE(A2,"mm","")),"X",REPT(" ",100)),100)/25.4,2)

HTH

[Marcol]

Hmm?
This might be a good case for getting the MoreFunc Add-in, then you can use EVAL()

This takes your text string directly to cubic inches

Please Login or Register  to view this content.

[Marcol]

Don't know what happened, but this should have been added to Post #6 ...

See this workbook for two ways without the MoreFunc Add-in

[rodmanlab]

Marcol, for some reason the workbook is not working and getting a #NAME? error

[rodmanlab]

Vandan, this worked like a charm! I need to make a few adjustments only.

Now the 2nd part of the question, how I reorganize the dimensions .... this database classifies hundreds of computer monitors, the norm should be H x W x D, but as you can tell, some dimensions are in different orders.

A few points for guidance:
- D (depth) should be the smallest dimension
- W (width) should be the largest dimension

[Marcol]

Marcol, for some reason the workbook is not working and getting a #NAME? error

This is because you don't have the MoreFunc Add-in.
The other columns should be okay.

[Marcol]

It might be easier to use a User Defined Function (UDF)

Put this in a Standard VBa Module

Please Login or Register  to view this content.

Use in Excel like so ...

Please Login or Register  to view this content.

When you open this attachment, allow macros.

[martindwilson]

i dont think op wants cubic inches but just converted so it reads 10 X100 X 0.5 inches
so using some ideas already posted to put in H W D order

[Marcol]

@ Martin

In Post #1 the OP seems to be trying to multiply three LOOKUP()s but can only get two.
The thread title seems, to me, to indicate a volume is ultimately required.

Maybe I'm reading things wrong, but it is not necessary to get each number first, then multiply them.

[EDIT]
Reading it again, maybe your right Martin.