Excel 2003 Split a column

  johnincrete 05:30 16 Jan 2014

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 2014

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.

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

InVision Studio takes on Adobe XD and Sketch

Camera tips to take better iPhone photos

Comment transformer un iPhone en borne Wi-Fi ?