Excel 2003 Split a column

  johnincrete 05:30 16 Jan 14
Locked

I have a spread sheet with a single colum the 400 entries to which are of the format:

  1. two digit number folowed by a space
  2. the word "song" followed by a space
  3. one or two digit number followed by a space
  4. the song title of vaiable length

I want to finish with two columns being the items 3 and 4 above.

Is it possible? I could eliminate the first two items by 20 replace edits but I would still need to split 3 & 4. The object of the exercise is to be able to sort by item 3

  Woolwell 10:15 16 Jan 14

In the next column you could use this formula =MID(A1,9,50). Where A1 is the first entry of your original data, A2 next, etc. 9 may need to be changed to 11 if you have "song" instead of song. I used 50 to allow for maximum title of 1 or 2 digits followed by a title. This number can be varied. This should give you a new column 1 or 2 digit number followed by a space and the song title. It should be possible to sort by the 1 or 2 digit number but it will be treated as text and therefore will not be in complete numerical order. separating 3 and 4 is more difficult because of the 1 or 2 digit. If I have time I'll have a go at a better formula.

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Here's what should be coming to Adobe Project Felix in 2017

Best iMovie for Mac tips 2017 | How to edit video with iMovie: 26 brilliant video editing tricks…