Odd MS Excel question

[summitlt]

I found this list and would love to be able to put it in an excel file and be able to sort it by 0-60 or whatever. I fI just copy it into excel it is just all the cars info in one cell so you cant sort it. Anyone know how to copy something and put every word in a seperate cell?

http://www.albeedigital.com/supercou...0-60times.html

[3.4 slow to go]

Re: Odd MS Excel question

try saving it as a text file, then importing w/delimiting (sp?) lines.

edit: n/m about delimiting, and adding the separation lines is more work than I want to do, but you cain go right ahead, just save as text, import external data, and add/sort columns as you see fit.

[zlexiss]

Re: Odd MS Excel question

Gonna be nasty. Think this does most everything.

1. First save to a text file. Delete all non-car rows. Delete all "(C&D Jan '69)" that come after some cars. Open in Excel using a fixed width column to split out the year and shove the everything else into one column.

2. Next make the times all the same width. To get single second times into two digits, replace stuff like " 9" (notice the space beforehand) with "09" and so on. All times should be "XX.X XX.X" before going on.

3. Use LEFT(LEN(cell)-7) to get the make/model without any of the times. Make this a new column.

4. Use MID(LEN(cel)-6,3) for 0-60 times.

5. Use RIGHT(3) to read the last 3 cells for the 1/4 mile time

6. Take all the two word model names and convert them to a single model with underscore using search and replace, ie "Aston Martin" to "Aston_Martin"

7a. TRIM(cell) which will delete the first space in the make/model cell (the one that came between the year and make)

7b. SUBSTITUTE(cell, " ", ";", 1) which will convert the first space in the make/model cell (the one between make and model) into a semicolon

8. Text to columns with a semicolon as the delimiter to split the make from the model.

9. Change "_" back to " " for aesthetics

Remember to copy and paste special>values on all your cells to convert the times to plain text before erasing the original mashed up cells you imported!

[zlexiss]

Re: Odd MS Excel question

Edit: after step 2 you're going to need to find all the times like "n/a" and convert them to something like 00.0 or 99.9

[TheGr8Schlotzky]

Re: Odd MS Excel question

I did some things with it.. Got real boring after a while though, so it's not complete. Here is what i have done: (excel file)

http://dl033.filefactory.com/dl/f/41...11996e76ceaee/

^^ you can sort by year or make, but the other stuff it only partially done.