removing superfluous information from rows; header length

trying2excel · Mar 30th, 2004, 08:36 PM

I can't find the beginner's section, so I post here. Apologies.

Yes, I have searched and searched to no avail--or at least I am not aware of having found any answers for the following, and I use Windows 2000 Pro and MS Excel 2000 (9.0.2720).

Is it possible to delete specific information from each of 12,000+ rows in a column other than doing this manually, line-by-line?

I have a LONG list in Excel of registered voters which contains columns with Names, Permanent Addresses, and Birth Dates. In each row under the Permanent Address column, the abbreviation "PERM ADDR:" pre-fixes each
voter's address. I see that I could delete each "PERM ADDR:" prefix manually, but this would take countless hours to perform. I am wanting the computer to do this work for me in a very few point-and-click moves.

Also, why on Earth is the header length in Excel limited to 255 characters? Is there any way around this deficiency?

Many thanks, T2E

Cbrine · Mar 30th, 2004, 08:40 PM

Insert a column after the address
In the first cell add the following formula

=mid(Cell Address,11,60)

Copy and Paste the data down the lenght of the entire column, or just filldown. Copy the new values and PasteSpecial... Values over the original column and then delete the formula column.

Hope this helps.

trying2excel · Mar 30th, 2004, 08:48 PM

It helps very much, Cbrine: thank you so much! I'll attempt to effect the operation you describe and report back later, hopefully with a success story.

Sincerely T2E

trying2excel · Apr 1st, 2004, 07:24 AM

Cbrine,
I couldn't get the formula method to work. I'm not saying it won't work, only that I was unable to work it

. I consulted another experts board, which I won't mention in this post, and the following procedure was shared. I was able to easily perform what would have been a mindnumbingly arduous task. Fantastic!

"You can get rid of it in one step by doing a global replace.

1) Click any single cell on the spreadsheet.

2) On the menu bar, go to Edit/Replace.

3) In the Find what box, enter "PERM ADDR:"

(Do not use the quote marks. Also, if there is a trailing space after the colon that you also want to remove, make sure to include it here.)

4) Make sure that the Replace with box is completely empty.

5) Click Replace All.

That string is now removed from the entire spreadsheet."

Mar 30th, 2004, 08:36 PM	#1
trying2excel Join Date: Mar 2004 Posts: 3	removing superfluous information from rows; header length I can't find the beginner's section, so I post here. Apologies. Yes, I have searched and searched to no avail--or at least I am not aware of having found any answers for the following, and I use Windows 2000 Pro and MS Excel 2000 (9.0.2720). Is it possible to delete specific information from each of 12,000+ rows in a column other than doing this manually, line-by-line? I have a LONG list in Excel of registered voters which contains columns with Names, Permanent Addresses, and Birth Dates. In each row under the Permanent Address column, the abbreviation "PERM ADDR:" pre-fixes each voter's address. I see that I could delete each "PERM ADDR:" prefix manually, but this would take countless hours to perform. I am wanting the computer to do this work for me in a very few point-and-click moves. Also, why on Earth is the header length in Excel limited to 255 characters? Is there any way around this deficiency? Many thanks, T2E

Mar 30th, 2004, 08:40 PM	#2
Cbrine Join Date: Dec 2003 Location: Brampton Posts: 3,174	Re: removing superfluous information from rows; header lengt Insert a column after the address In the first cell add the following formula =mid(Cell Address,11,60) Copy and Paste the data down the lenght of the entire column, or just filldown. Copy the new values and PasteSpecial... Values over the original column and then delete the formula column. Hope this helps. __________________ Xcelerated Solutions Office Automation Solutions for the Toronto Area

Mar 30th, 2004, 08:48 PM	#3
trying2excel Join Date: Mar 2004 Posts: 3	Re: removing superfluous information from rows; header lengt It helps very much, Cbrine: thank you so much! I'll attempt to effect the operation you describe and report back later, hopefully with a success story. Sincerely T2E

Apr 1st, 2004, 07:24 AM	#4
trying2excel Join Date: Mar 2004 Posts: 3	Re: removing superfluous information from rows; header lengt Cbrine, I couldn't get the formula method to work. I'm not saying it won't work, only that I was unable to work it . I consulted another experts board, which I won't mention in this post, and the following procedure was shared. I was able to easily perform what would have been a mindnumbingly arduous task. Fantastic! "You can get rid of it in one step by doing a global replace. 1) Click any single cell on the spreadsheet. 2) On the menu bar, go to Edit/Replace. 3) In the Find what box, enter "PERM ADDR:" (Do not use the quote marks. Also, if there is a trailing space after the colon that you also want to remove, make sure to include it here.) 4) Make sure that the Replace with box is completely empty. 5) Click Replace All. That string is now removed from the entire spreadsheet."

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode