Meaningful list

[tigerzen]

I've got some data which is presented in this way, there are thousands of entries and I'm open to any method (multi-step is okay) that will cut down on time and will return song in one column and artist in another. Desired format is in column D. A 2 column approach is what I'm after. Any suggestions?

Solfege (version 2) (version 1).xlsb
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
2	SONG LIST Already Gone Powderfinger All For You Sister Hazel And we danced The Hooters All you need is love Beatles Amazing Alex Loyd Arms wide open Creed All I want is you U2 All night long Lionel Ritchie Are you gonn go my way Lenny Kravitz Boulevard Broken Dreams Green Day Beast of Burden Rolling Stones Barbara Ann Beach Boys Baby I love your Way Peter Frampton
3
4		Desired format
5		Already gone	Powderfinger
6
Sheet3

 

[kweaver]

There is no way to clearly separate the song from the artist so I personally can't imagine there's a simple (or even complex) method (unless you had at least table of artists).
You can that even Flash Fill can't make a suggestion.

 

[tigerzen]

We're on the same wavelength, I think I can put together a table of artists pretty quickly but what to do after that?

 

[kweaver]

Not sure if this is OK w/you, but if the artists were in the same order as they appear in the long list, try this:

Book1
	C	D
1	SONG LIST
2	Powderfinger	Already Gone
3	Sister Hazel	All For You
4	The Hooters	And we danced
5	Beatles	All you need is love
6	Alex Loyd	Amazing
7	Creed	Arms wide open
8	U2	All I want is you
9	Lionel Ritchie	All night long
10	Lenny Kravitz	Are you gonn go my way
11	Green Day	Boulevard Broken Dreams
12	Rolling Stones	Beast of Burden
13	Beach Boys	Barbara Ann
14	Peter Frampton	Baby I love your Way
Sheet3

Cell Formulas
Range	Formula
D2:D14	D2	=MID($A$2,SEARCH(C1,$A$2)+LEN(C1)+1,SEARCH(C2,$A$2)-SEARCH(C1,$A$2)-LEN(C1)-2)

If you have to add to the long list, add the respective artists to the C column. There can't be repeated artists in this approach, however.

 

[tigerzen]

Not sure if this is OK w/you, but if the artists were in the same order as they appear in the long list, try this:

Book1
	C	D
1	SONG LIST
2	Powderfinger	Already Gone
3	Sister Hazel	All For You
4	The Hooters	And we danced
5	Beatles	All you need is love
6	Alex Loyd	Amazing
7	Creed	Arms wide open
8	U2	All I want is you
9	Lionel Ritchie	All night long
10	Lenny Kravitz	Are you gonn go my way
11	Green Day	Boulevard Broken Dreams
12	Rolling Stones	Beast of Burden
13	Beach Boys	Barbara Ann
14	Peter Frampton	Baby I love your Way
Sheet3

Cell Formulas
Range	Formula
D2:D14	D2	=MID($A$2,SEARCH(C1,$A$2)+LEN(C1)+1,SEARCH(C2,$A$2)-SEARCH(C1,$A$2)-LEN(C1)-2)

If you have to add to the long list, add the respective artists to the C column. There can't be repeated artists in this approach, however.

Thanks Dreamweaver I can work with this, it's very good, artists are repeated but this will help cut down on a lot of manual work.

 

[kweaver]

Glad I could help some. Maybe future listings should take on a much better organization.

 

[kweaver]

You might also try:

=TRIM(TEXTAFTER(TEXTBEFORE($A$2,C2),C1))