Segmenting text in various columns

[canadian86]

hey guys,

I have this text in 3 rows in Google Sheets:

Are You Compelling - 133 Likes - 35 Comments - 3450 Views.mp4

Are You Excited - 193 Likes - 46 Comments - 5245 Views.mp4

Be The Promoter - 81 Likes - 59 Comments - 1168 Views.mp4

I want to output each row in different columns.

So, I want it to show:

Cell B1 = Are You Compelling
Cell B2 = 133 Likes
Cell B3 = 35 Comments
Cell B4 - 3450 Views

Any idea how I can achieve this?

 

[AnAppleADay]

Hi there,

I’m not on my laptop at the moment (using my phone), but I think there’s a function you can use on the Dat all’s tan called ‘Text to Columns’, it may just be a button. If you select that, then option ‘Delimited’ and define ‘-‘ (hyphen) as your character.

If you want to remove all the instances of ‘.mp4’. Highlight all and use the ‘Find & Replace’ function, enter ‘.mp4’ and leave the ‘Replace with’ field blank.

Good luck

 

[kennypete]

With formulas for all three:

segmenting-text-in-various-columns.1130375.xlsx
	A	B
1	Are You Compelling - 133 Likes - 35 Comments - 3450 Views.mp4	Are You Compelling
2		133 Likes
3		35 Comments
4		3450 Views
5	Are You Excited - 193 Likes - 46 Comments - 5245 Views.mp4	Are You Excited
6		193 Likes
7		46 Comments
8		5245 Views
9	Be The Promoter - 81 Likes - 59 Comments - 1168 Views.mp4	Be The Promoter
10		81 Likes
11		59 Comments
12		1168 Views
Sheet1 (3)

Cell Formulas
Range	Formula
B1	B1	=TRIM(LEFT($A$1,FIND("^",SUBSTITUTE($A$1,"-","^",1),1)-2))
B2	B2	=TRIM(MID($A$1,FIND("^",SUBSTITUTE($A$1,"-","^",1),1)+2,FIND("^",SUBSTITUTE($A$1,"-","^",2),1)-FIND("^",SUBSTITUTE($A$1,"-","^",1),1)-2))
B3	B3	=TRIM(MID($A$1,FIND("^",SUBSTITUTE($A$1,"-","^",2),1)+2,FIND("^",SUBSTITUTE($A$1,"-","^",3),1)-FIND("^",SUBSTITUTE($A$1,"-","^",2),1)-2))
B4	B4	=TRIM(MID($A$1,FIND("^",SUBSTITUTE($A$1,"-","^",3),1)+2,LEN(A1)-FIND("^",SUBSTITUTE($A$1,"-","^",3),1)-5))
B5	B5	=TRIM(LEFT($A$5,FIND("^",SUBSTITUTE($A$5,"-","^",1),1)-2))
B6	B6	=TRIM(MID($A$5,FIND("^",SUBSTITUTE($A$5,"-","^",1),1)+2,FIND("^",SUBSTITUTE($A$5,"-","^",2),1)-FIND("^",SUBSTITUTE($A$5,"-","^",1),1)-2))
B7	B7	=TRIM(MID($A$5,FIND("^",SUBSTITUTE($A$5,"-","^",2),1)+2,FIND("^",SUBSTITUTE($A$5,"-","^",3),1)-FIND("^",SUBSTITUTE($A$5,"-","^",2),1)-2))
B8	B8	=TRIM(MID($A$5,FIND("^",SUBSTITUTE($A$5,"-","^",3),1)+2,LEN($A$5)-FIND("^",SUBSTITUTE($A$5,"-","^",3),1)-5))
B9	B9	=TRIM(LEFT($A$9,FIND("^",SUBSTITUTE($A$9,"-","^",1),1)-2))
B10	B10	=TRIM(MID($A$9,FIND("^",SUBSTITUTE($A$9,"-","^",1),1)+2,FIND("^",SUBSTITUTE($A$9,"-","^",2),1)-FIND("^",SUBSTITUTE($A$9,"-","^",1),1)-2))
B11	B11	=TRIM(MID($A$9,FIND("^",SUBSTITUTE($A$9,"-","^",2),1)+2,FIND("^",SUBSTITUTE($A$9,"-","^",3),1)-FIND("^",SUBSTITUTE($A$9,"-","^",2),1)-2))
B12	B12	=TRIM(MID($A$9,FIND("^",SUBSTITUTE($A$9,"-","^",3),1)+2,LEN($A$9)-FIND("^",SUBSTITUTE($A$9,"-","^",3),1)-5))