Deleting Data in an Excel file

arlo · 09-15-2005, 11:11 PM

I am doing a school directory, and have a list of 400 students, each entry looking roughly like this, with variations in length:

Smith, Mary
1234 Main St., 831-444-4444
thisismyemail@juno.com
Parents: Ike and Tina Smith
ikeand tina@juno.com

I want to produce a first-name list, and want to end up with Mary and Smith in separate columns that I can then sort. In the end, my list will look like this:

Mary Smith
Mary Turner
Maryanne Jones
Nicholas Jones
Nicole Adams
etc.

My list is in Word. I imagine Excel would be better for the first name list. I think I know how to put my list into Excel, but how can I then delete all the data and be left with just "Mary" and "Smith" without going through and manually deleting the unwanted rows for all 400 students?

Thanks for any guidance on this.

**Glaswegian** · 09-16-2005, 02:03 PM

Hi

If you can get your list into Excel, it would make it much easier.

Is the format always the same? From your example, are there always 5 lines of data?

Regards

arlo · 09-16-2005, 11:43 PM

Yes, I can get it into Excel. But no, each entry varies in length since some families have two households, some don't want email listed, etc. So they vary from 3 lines to maybe as many as 8.

One thing I didn't mention is that in my Word list, after the student's name, on the same line, I have 4 tabs and then the number of the student's grade level. There are no tabs anywhere else. My husband said he thinks those tabs can serve as a marker, to differentiate the name line from the others.

Any thought on this?

Thanks for your help.

**Glaswegian** · 09-17-2005, 03:34 AM

Hi arlo

They could indeed serve as a marker, although there are several ways to do what you need. Firstly, get your list into Excel. When you copy and paste, the name details etc on each line in Word should go in a separate row in Excel. If the grade marker is several columns away from the main data, delete any blank columns so that you end up with data in columns A and B, and each line of data in it's own row.

After that, some formulae and some code should do the trick. It might be easier if you upload a sample Excel file (use the 'Manage Attachments' button when posting) - please delete any sensitive data if required. Any problems with any of this just post back and we'll sort it out for you.

Regards

09-15-2005, 11:11 PM	#1 (permalink)
arlo Registered User Join Date: Oct 2004 Posts: 12 OS: Windows 2000	Deleting Data in an Excel file I am doing a school directory, and have a list of 400 students, each entry looking roughly like this, with variations in length: Smith, Mary 1234 Main St., 831-444-4444 thisismyemail@juno.com Parents: Ike and Tina Smith ikeand tina@juno.com I want to produce a first-name list, and want to end up with Mary and Smith in separate columns that I can then sort. In the end, my list will look like this: Mary Smith Mary Turner Maryanne Jones Nicholas Jones Nicole Adams etc. My list is in Word. I imagine Excel would be better for the first name list. I think I know how to put my list into Excel, but how can I then delete all the data and be left with just "Mary" and "Smith" without going through and manually deleting the unwanted rows for all 400 students? Thanks for any guidance on this.

09-16-2005, 02:03 PM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,512 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi If you can get your list into Excel, it would make it much easier. Is the format always the same? From your example, are there always 5 lines of data? Regards __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

09-17-2005, 03:34 AM	#4 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,512 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi arlo They could indeed serve as a marker, although there are several ways to do what you need. Firstly, get your list into Excel. When you copy and paste, the name details etc on each line in Word should go in a separate row in Excel. If the grade marker is several columns away from the main data, delete any blank columns so that you end up with data in columns A and B, and each line of data in it's own row. After that, some formulae and some code should do the trick. It might be easier if you upload a sample Excel file (use the 'Manage Attachments' button when posting) - please delete any sensitive data if required. Any problems with any of this just post back and we'll sort it out for you. Regards __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

09-16-2005, 11:43 PM	#3 (permalink)
arlo Registered User Join Date: Oct 2004 Posts: 12 OS: Windows 2000	Yes, I can get it into Excel. But no, each entry varies in length since some families have two households, some don't want email listed, etc. So they vary from 3 lines to maybe as many as 8. One thing I didn't mention is that in my Word list, after the student's name, on the same line, I have 4 tabs and then the number of the student's grade level. There are no tabs anywhere else. My husband said he thinks those tabs can serve as a marker, to differentiate the name line from the others. Any thought on this? Thanks for your help.