Separate text from one cell into multiple cells and convert them into decimals

I'm currently taking data from BlueBeam and exporting it into excel. One of my columns is a steel shape column (i.e. W14x730, L3x3x3/8, PL1 1/2"x5", etc.). I am trying to get an IF statement that will only pull information from shapes that are plates (PL). Once the column recognizes the correct shape I need to pull the thickness (1 1/2") and the width (5") into two separate columns and have them calculate into decimals. It sounds like the formula will be pretty involved but I just cant get it to work. Please see example below:


A B C
1 W14x730 - -
2 L3x3x3/8 - -
3 PL1 1/2"x5" 1.5 5
4 PL1/2"x4 1/2" 0.5 4.5
5 PL2 1/2"x23" 2.5 23

Sorry - My example didn't hold the spaces. Hopefully this helps.

____A__________B____________C
1 W14x730_______-____________-
2 L3x3x3/8_______-____________-
3 PL1 1/2"x5"____1.5___________5
4 PL1/2"x4 1/2"___0.5__________4.5
5 PL2 1/2"x23"___2.5__________23

You have only shared 5 examples (only 3 of which need to return anything). The following formulas work for your examples but will need to be added to if, for example, fractions other than 1/2 are used.

B1 =IFERROR(LEFT(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1/2""",".5")," ",""),"""",""),FIND("PL",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1/2""",".5")," ",""),"""",""))+2,100),FIND("x",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1/2""",".5")," ",""),"""",""),FIND("PL",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1/2""",".5")," ",""),"""",""))+2,100))-1)+0,"")

C1 =IFERROR(MID(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1/2""",".5")," ",""),"""",""),FIND("PL",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1/2""",".5")," ",""),"""",""))+2,100),FIND("x",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1/2""",".5")," ",""),"""",""),FIND("PL",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1/2""",".5")," ",""),"""",""))+2,100))+1,100)+0,"")

I attached a more detailed example of the shapes I will be looking at. The dimensions will always be different so the formula will need to account for that. The BlueBeam export I provided is only one table of thousands I need to evaluate. That is why I'm looking for a formula to pull this information out of the shape description.

These should work.

B2 =IFERROR((IF(ISNUMBER(FIND(" ",LEFT(MID(A2,FIND("PL",A2)+2,100),FIND("X",MID(A2,FIND("PL",A2)+2,100))-1))),"","0 ")&SUBSTITUTE(LEFT(MID(A2,FIND("PL",A2)+2,100),FIND("X",MID(A2,FIND("PL",A2)+2,100))-1),"""",""))+0,"")

C2 =IF(B2="","",SUBSTITUTE(IF(AND(ISERROR(FIND(" ",MID(A2,FIND("X",A2)+1,100))),ISNUMBER(FIND("/",MID(A2,FIND("X",A2)+1,100)))),"0 ","")&MID(A2,FIND("X",A2)+1,100),"""","")+0)

See attachment.

Yes, those formulas are working great for me! Thank you very much, I really appreciate your help!

Happy to help. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Actually I tried using the formula on the plate shape PL2"X6" and the formula won't work... Is there a reason for that?

Or……….

1] In B2, copied down :

=IF(LEFT($A2,2)="PL",--(IF(MID($A2,4,1)=" ",0,"0 ")&MID($A2,3,FIND("X",$A2)-4)),"")

2] In C2, copied down :

=IF(LEFT($A2,2)="PL",--MID($A2,FIND("X",$A2)+1,LEN($A2)-FIND("X",$A2)-1),"")

Regards
Bosco

Bosco - Thank you for your reply. I put your formula in too and it also works. The odd thing is is Falcondude's works for certain one and yours works for others. Is there a way to fix this?

These work in your sample from post #11:

B2 =IFERROR((IF(AND(ISERROR(FIND(" ",SUBSTITUTE(LEFT(MID(A2,FIND("PL",A2)+2,100),FIND("X",MID(A2,FIND("PL",A2)+2,100))-1),"""",""))),ISNUMBER(FIND("/",SUBSTITUTE(LEFT(MID(A2,FIND("PL",A2)+2,100),FIND("X",MID(A2,FIND("PL",A2)+2,100))-1),"""","")))),"0 ","")&SUBSTITUTE(LEFT(MID(A2,FIND("PL",A2)+2,100),FIND("X",MID(A2,FIND("PL",A2)+2,100))-1),"""",""))+0,"")

C2 =IF(B2="","",SUBSTITUTE(IF(AND(ISERROR(FIND(" ",MID(A2,FIND("X",A2)+1,100))),ISNUMBER(FIND("/",MID(A2,FIND("X",A2)+1,100)))),"0 ","")&MID(A2,FIND("X",A2)+1,100),"""","")+0)

Just a bit adjusted……….

1] In B2, copied down :

=IF(LEFT($A2,2)="PL",--(IF(MID($A2,4,1)="/","0 ",0)&MID($A2,3,FIND("X",$A2)-4)),"")

1] In C2, copied down (unchanged):

=IF(LEFT($A2,2)="PL",--MID($A2,FIND("X",$A2)+1,LEN($A2)-FIND("X",$A2)-1),"")

Regards
Bosco

Thank you both for all of your help! Everything seems to be working correctly now. Again, I appreciate it!