Hi,
I need a little help with a formula.
In column "A" I have dimensions like this in several rows.
I need to put each dimension in column "B", column "C" and for the "Third" dimension column "D".
I have a formula in column "B" for the first dimension, column C for the second dimension. and column "D" for the third dimension. But, the formula in column "D" will not give me the correct data. It give me the second dimension.
Here's the formula I used for the third dimension.(columnD). I used column A thu D for this post.
Row 2 Column A: 234 x 34 x 96
Here's where I'm having a problem
Row 3 Column A: 356 x 22
'''' note this row only has 2 dimensions and the formula show will show the second dimension in column D
The formulas I have for column B and C works just fine.
For the above situation I did rece a answer.
Original posted at Custom Formula • ExcelKey.com - Free Excel Help
Thanks
I need a little help with a formula.
In column "A" I have dimensions like this in several rows.
I need to put each dimension in column "B", column "C" and for the "Third" dimension column "D".
I have a formula in column "B" for the first dimension, column C for the second dimension. and column "D" for the third dimension. But, the formula in column "D" will not give me the correct data. It give me the second dimension.
Here's the formula I used for the third dimension.(columnD). I used column A thu D for this post.
Row 1 Column A: 12 x 12 x12=RIGHT($L2,LEN($L2)-FIND("*",SUBSTITUTE($L2," ","*",LEN($L2)-LEN(SUBSTITUTE($L2," ","")))))
Row 2 Column A: 234 x 34 x 96
Here's where I'm having a problem
Row 3 Column A: 356 x 22
'''' note this row only has 2 dimensions and the formula show will show the second dimension in column D
The formulas I have for column B and C works just fine.
For the above situation I did rece a answer.
Assuming your data is consistent in using "x" to separate your dimensions, try this for your third dimension:
=IFERROR(MID(SUBSTITUTE($A2," ",""),SEARCH("x",SUBSTITUTE(SUBSTITUTE($A2," ",""),"x","z",1))+1,100),"")
ConneXionLost,
Thanks for the code. It work great. However I mis spoked about the formula I have for column "C".
I discovered this when I applied your formula and check the data that I have the column did not show what I expected for the following examples.
In Column A I have dimension like:
15 x 5 x 12''' you will note that for the second dimension there is only 1 character. The code I have for this returns "5 x"
=MID($L910,SEARCH("x",$L910,1)+1,SEARCH("x",$L910,SEARCH("x",$L910,1)+1-SEARCH("x",$L910,1)))
Also if i I have
24.625 x 8.25 x 40
My formula returns "8.25 x"
Original posted at Custom Formula • ExcelKey.com - Free Excel Help
Thanks