New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Custom Formula

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Custom Formula

Postby charles » Wed Mar 30, 2016 7:49 pm

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.


Thanks
  • 0

There are other ways too do this but...

Be kind let us know if you posted somewhere else!



Charles
charles
Excel Badass
 
Posts: 632
Joined: Dec 10, 2011
Location: Mississippi
Reputation: 1
Excel Version: Excel 2003, 2007 and Mac 2011

Re: Custom Formula

Postby ConneXionLost » Thu Mar 31, 2016 11:45 am

Assuming your data is consistent in using "x" to separate your dimensions, try this for your third dimension:

Code: Select all
=IFERROR(MID(SUBSTITUTE($A2," ",""),SEARCH("x",SUBSTITUTE(SUBSTITUTE($A2," ",""),"x","z",1))+1,100),"")


Cheers,
  • 5

ConneXionLost
Regular
 
Posts: 68
Joined: May 9, 2013
Location: Canada
Reputation: 19
Excel Version: 2003, 2010

Re: Custom Formula

Postby charles » Thu Mar 31, 2016 12:42 pm

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"

Thanks
  • 0

There are other ways too do this but...

Be kind let us know if you posted somewhere else!



Charles
charles
Excel Badass
 
Posts: 632
Joined: Dec 10, 2011
Location: Mississippi
Reputation: 1
Excel Version: Excel 2003, 2007 and Mac 2011

Re: Custom Formula

Postby charles » Thu Mar 31, 2016 2:47 pm

HI,

I posted this same question at http://www.mrexcel.com/forum/excel-ques ... rmula.html

This by no means that I do not appreciate "ConneXionLost" help. I certainly do.

If I receive a answer from "MrExcel" I will share it here.
As always I do appreciate the help I receive here.

Charles
  • 0

There are other ways too do this but...

Be kind let us know if you posted somewhere else!



Charles
charles
Excel Badass
 
Posts: 632
Joined: Dec 10, 2011
Location: Mississippi
Reputation: 1
Excel Version: Excel 2003, 2007 and Mac 2011

Re: Custom Formula

Postby charles » Thu Mar 31, 2016 3:35 pm

Ok,

Here's the response that I received.
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.


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)))


ConneXionLost I appreciate your help.

Charles
  • 0

There are other ways too do this but...

Be kind let us know if you posted somewhere else!



Charles
charles
Excel Badass
 
Posts: 632
Joined: Dec 10, 2011
Location: Mississippi
Reputation: 1
Excel Version: Excel 2003, 2007 and Mac 2011


Return to General Excel Questions

Who is online

Users browsing this forum: Google [Bot] and 48 guests