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

[skatar90]

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

[skatar90]

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

[Glenn Kennedy]

Please post this as a sample spreadsheet. Also, please check that No 4 is correct....

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[63falcondude]

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.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[skatar90]

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.

[63falcondude]

These should work.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

See attachment.

[skatar90]

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

[63falcondude]

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.

[Bosco]

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

[skatar90]

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

[skatar90]

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?

[63falcondude]

These work in your sample from post #11:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Bosco]

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

[skatar90]

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