Formula to pull specific characters from a text string into another cell

[Mike Lindsey]

I have a 200k row spreadsheet that has information such as (1-1/8" X 1040') or (3/4" X 428') included, I would like to be able to run a lookup that will separate the diameter and the lengths (Diameter X Length) into their own cells, I have tried different methods however I seem to be having problems caused by the inch and feet symbols.
Would someone have an example of a formula that will work?

 

[dreid1011]

If your data is formatted exactly as you are showing then you can try the TEXTBEFORE and TEXTAFTER functions.

=TEXTBEFORE(A2," X ")
=TEXTAFTER(A2," X ")

Book1
	A	B	C
1
2	(1-1/8" X 1040')	1-1/8"	1040'
3	(3/4" X 428')	3/4"	428'
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=TEXTAFTER(TEXTBEFORE(A2," X "),"(")
C2:C3	C2	=TEXTBEFORE(TEXTAFTER(A2," X "),")")

Or

Book1
	D	E
2	1-1/8"	1040'
Sheet2

Cell Formulas
Range	Formula
D2	D2	=SUBSTITUTE(TEXTBEFORE(A2," X "),"(","")
E2	E2	=SUBSTITUTE(TEXTAFTER(A2," X "),")","")

 

[Mike Lindsey]

dreid1011​

Thanks for your help!

 

[dreid1011]

You're welcome, and happy to help.

 

[Cubist]

You can get a spill formula with TEXTSPLIT.

Book1
	A	B	C
1
2	(1-1/8" X 1040')	1-1/8"	1040'
3	(3/4" X 428')	3/4"	428'
Sheet3

Cell Formulas
Range	Formula
B2:C3	B2	=TEXTSPLIT(A2,{"("," X ",")"},,1)
Dynamic array formulas.