Excel Numbering

**GH0STce11** · May 30th, 2006, 06:41 PM

Hey, I'm making and excel spreadsheet with a huge playlist of songs.

Basically i want to make a column starting at the number 500, and ending at number 1.

and also, not necassary, but the song tile and artist name are on the same line and column. and separated by a lot of spaces. any way to spilt them into two seperate coumns?

thanks!

**Jarhed7276** · May 30th, 2006, 08:34 PM

The numbering part is easy. Just start with 500, go to the next row (same column) and key in 499, go to the next row (same column) and key in 498. Then highlight all three cells and drag down to the 500th row. You'll see the numbers decreased to 1.

The second part...not so easy. Are the 2 separate items always found in the same position in the cell, or are they all over? For example, is the Album title always the first 30 characters of the cell and the Artist the 31th - 50th characters? If so, splitting may be possible. If not, hmmm...not sure how to do that.

**DanU** · May 31st, 2006, 02:15 PM

How many spaces separate the artist from the title? Search and relpace the exact number of spaces with another character, like a comma. Then use the "Text to Columns" tool (under the Data menu) and select the comma delimiter to split the artist from the title

**GH0STce11** · May 31st, 2006, 05:37 PM

ok, got the numbering done...thanks!

now for the naming.

there's always four spaces at the beginning, and then the song name.

the bad part is that the spaces seperating the song title from the artist name varies. I think it's based on how long the song title is but i can't be sure.

here's a screenshot of my sheet:

**DanU** · June 1st, 2006, 12:00 PM

That definitely looks like variable spacing, which complicates things but it's still possible to separate them in excel.

Try searching for TWO spaces and replacing it with one comma, then use the text to columns tool and select the comma delimiter, and also check "treat consecutive delimiters as one". This will work so long as there aren't two spaces in the song name or artist. Also if you have an odd number of spaces then you'll be left with an extra space in there somewhere.

A more foolproof option is to try search and replacing for the maximum number of consecutive spaces you think exist between the two columns and replacing it with one comma, then search again for one less number of spaces and replacing it with one comman, then repeat again for two less spaces than max. Keep replacing fewer and fewer numbers of consecutive spaces with commas until you've replaced all of the delimiting spaces with single commas. THEN use the text to columns tool.