Print list of albums in Excel by artist

[Mike Mintrum]

Im sure there is an easy way to do what i want to do but my excel is a bit rusty so apologises if someone has already answered this.

If I list albums from the B2 I get a .txt file. This I can import to excel and by using the "/" delimiter I get two columns a) Artist and b) Album title. However the Artist column entry starts with the number of the entry before the artist name. I want to remove these number s so I can sort the file by artist alphabetically. Im sure this must be easy but I  cant recall how to do it so help advice appreciated.

Thanks

Mike

[KJ Palmer]

Not sure of the exact format from the B2, but if it's n + space + artist name, this horrible looking formula should work if you copy and paste it into a spare column.

=TRIM(RIGHT(A2,LEN(A2)-SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},"")))))

Change the A2 for the cell of the first artist and propagate down for all rows. Then you can copy and paste values for the entire column. 

Hope it does the trick, sure there must be a better way, but it worked well for a column of addresses I tried it on.

[Peter Gilbert]

Hi

You can also use this formula.

=RIGHT(A2,(LEN(A2)-(FIND(" ",A2,1))))

This will strip out the number and the space leaving behind just the artist's name. Propagate down the sheet. The formula finds the location of the space in the whole text string, then deducts that figure from the length of the text string, and then takes everything to the right of that position, leaving just the artist's name.

If you then want just the raw data, copy and paste special and select 'Values' to ditch the formula.

Peter