convert # x # dimensions to decimal for panel size

[solidENM]

Hello,
I am hoping someone has some experience taking dimensions and converting them into decimal format.
I have made some code to extract all panels from a list and create a new sheet called "Panels." I need to take this one step further, and convert the # x # format into a decimal number, preferably rounded to nearest .25 of a whole panel.
in column C, I have dimensions of a panel.

EX: 500x600mm. This would be converted to 0.25 panels.
EX: 900x1000 would be 0.5 panels.

Total panel size is 1220 x 2440. They always have the same format, with the space between the number on either side, and the x in the middle.


col a: item number
col b: qty
Col C: dimensions

I would like to convert column C to decimal into column "e" which is empty.

 

[63falcondude]

Maybe you can do something like this? Note that this only works for dimensions in "#x#" format without the "mm" or spaces.

Excel 2010
	A	B	C	D
1	Item #	Qty	dimensions	Panels
2	123	1	500x600	0.25
3	456	1	900x1000	0.5
Sheet1

Cell Formulas
Range	Formula
D2	=LOOKUP(LEFT(C2,FIND("x",C2)-1)*MID(C2,FIND("x",C2)+1,LEN(C2)),{0,744201,1488401},{0.25,0.5,1})

 

[solidENM]

WOW, that worked great. thanks for the help! it seems to be working fine both with and without the spaces too. I tried both just to make sure.

 

[solidENM]

would we be able to throw a modifier in there? Column B has the quantity of each panel. I added ( ) *b3 at the end, but it will just multiply the end result. for instance, 3 panels at 411 x 271 is coming in at .75 when .25 would be barely under the actual number.

Could we have the lookup find which number is smaller in column C and multiply it by the qty in column B before finally multiplying that by the larger number in column c?

EX: 411 x 271 (at qty 3 in column B)
411 x 813


This would produce a much closer estimation for my purposes.

 

[63falcondude]

would we be able to throw a modifier in there? Column B has the quantity of each panel. I added ( ) *b3 at the end, but it will just multiply the end result. for instance, 3 panels at 411 x 271 is coming in at .75 when .25 would be barely under the actual number.

Could we have the lookup find which number is smaller in column C and multiply it by the qty in column B before finally multiplying that by the larger number in column c?

EX: 411 x 271 (at qty 3 in column B)
411 x 813


This would produce a much closer estimation for my purposes.

It doesn't matter if you multiply the first or second # by 3.
3*L*W = L*3*W = L*W*3.

You can change the formula to something along the lines of

=LOOKUP(B2*LEFT(C2,FIND("x",C2)-1)*MID(C2,FIND("x",C2)+1,LEN(C2)),{0,744201,1488401,...},{0.25,0.5,1,...})

but it will need more numbers at the end to account for the larger value due to the qty multiple.

 

[solidENM]

thanks again falcondude. the code itself is working great. You saved me a bunch of time.

 

[63falcondude]

thanks again falcondude. the code itself is working great. You saved me a bunch of time.

Great! Glad to help.