How to Separate Parts of Text from One Cell into Multiple

[ikedawood]

Hello,

I have a string of text in a cell that I need to separate into multiple cells, Text to Columns will work however I need a formula to do this as I don't want to have to break this out every time I refresh the data.

I don't think LEFT, RIGHT, or MID functions will work since the number of characters may vary. A2 reads: A-Frames (88), Baselines (2), Courtsides (3), Dasherboards (14), Home Plate (5), Small Home Plate (6), Other (27)

The text, parentheses, and commas will remain the same on each row in column A. However, the number next to each will vary. I need a formula in cells B2:H2 that will pull in the appropriate data as noted below.

[TABLE="class: grid, width: 1200"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Types and Quantity[/TD]
[TD]A-Frames[/TD]
[TD]Baselines[/TD]
[TD]Courtsides[/TD]
[TD]Dasherboards[/TD]
[TD]Home Plate[/TD]
[TD]Small Home Plate[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]A-Frames (88), Baselines (2), Courtsides (3), Dasherboards (14), Home Plate (5), Small Home Plate (6), Other (27)[/TD]
[TD]88[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]14[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]27[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to MrExcel.
If you're interested in a UDF how about

Function Mysplit(cl As Range, itm As Long) As Long
   Dim Sp As Variant
   Sp = Split(cl, "(")
   Mysplit = Split(Sp(itm), ")")(0)
End Function

Used like =Mysplit($A2,COLUMN(A1))

 

[jtakw]

Hi,

Use B2 formula copied across, results are Text.
Use B3 formula copied across, results converted to Real Numbers:

Book1
	A	B	C	D	E	F	G	H
1	Types and Quantity	A-Frames	Baselines	Courtsides	Dasherboards	Home Plate	Small Home Plate	Other
2	A-Frames (88), Baselines (2), Courtsides (3), Dasherboards (14), Home Plate (5), Small Home Plate (6), Other (27)	88	2	3	14	5	6	27
3		88	2	3	14	5	6	27
Sheet553

Cell Formulas
Range	Formula
B2	=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2&",","),","("),"(",REPT(" ",100)),(COLUMNS($B2:B2)*2-1)*100,100))
B3	=MID(SUBSTITUTE(SUBSTITUTE($A2&",","),","("),"(",REPT(" ",100)),(COLUMNS($B2:B2)*2-1)*100,100)+0

 

[ikedawood]

jtakw, that works perfectly!!! Thanks so much for the help on that.

If down the road I was to add a new option, say after "Other (###)" I was to add an additional ", TEXT (###)". How would I alter the formula when adding a column I? Again the common, parenthesis, and text would remain consistent, just the number would change.

 

[jtakw]

You're welcome, welcome to the forum.

My formula will actually accommodate as Many extractions as needed, the Only part of the formula that needs changing is the number 100, due to the longer original text string in A2, for now we'll change it to 255, which should be plenty for your purposes:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Types and Quantity	A-Frames	Baselines	Courtsides	Dasherboards	Home Plate	Small Home Plate	Other
2	A-Frames (88), Baselines (2), Courtsides (3), Dasherboards (14), Home Plate (5), Small Home Plate (6), Other (27), Text(123), More Text (456), Even More Text (789)	88	2	3	14	5	6	27	123	456	789
3		88	2	3	14	5	6	27	123	456	789
Sheet553

Cell Formulas
Range	Formula
B2	=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2&",","),","("),"(",REPT(" ",255)),(COLUMNS($B2:B2)*2-1)*255,255))
B3	=MID(SUBSTITUTE(SUBSTITUTE($A2&",","),","("),"(",REPT(" ",255)),(COLUMNS($B2:B2)*2-1)*255,255)+0

The formula will extract correctly as long as your original text string does not exceed 250+ characters, if it Does, then you can change all instances of 255 to a higher number or LEN(A2)