Custom Formula

[CharlesH]

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.

=RIGHT($L2,LEN($L2)-FIND("*",SUBSTITUTE($L2," ","*",LEN($L2)-LEN(SUBSTITUTE($L2," ","")))))

Row 1 Column A: 12 x 12 x12
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

 

[Scott Huish]

You can actually do this with Text to Columns
Select Column A.
Text to Columns
Delimited
x and space as your delimiters
Click Next
In Destination, type B1 and click Finish.

 

[CJ_22]

Paste this into B1 and drag to D1 then drag down.

=TRIM(MID(SUBSTITUTE($A1,"x",REPT(" ",LEN($A1))),((COLUMNS($A1:A1)-1)*LEN($A1))+1,LEN($A1)))

 

[CharlesH]

HI,

Thanks for both suggestion.
Scott I went with the formula provided by CJ_22.

Both were great.

Thanks