[SOLVED] Excel question - deleting/hiding certain data in a column

Need2BAGeek · 04-02-2008, 05:19 PM

Before I explain my issue, my system is running WinXP Pro SP2 and I'm using Excel 2002. If you need more info, just ask.

My issue is that I have a spreadsheet that was created by exporting data from a third-party application and one of the columns displays too much info. The column I'm referring to contains file names, which are in the format "##-### - <text>.bin". So all 5,000+ rows in this column have "##-### - <text>.bin" in it, but I need the column to only have the first 6 characters, "##-###". The first 6 characters are a unique identifier comprised of 2 digits, a hyphen, and 3 more digits. I'd like to delete/hide the remainder of the file name " - <text>.bin" so users aren't confused. Are there any functions in Excel that allow me to delete all data in this column except the first 6 characters of each cell? I can't find a way.

Please let me know if anyone has any ideas. For printing purposes, I can change the column width to hide everything except the first 6 characters. But for the purpose of sharing this file with other users, I'd like to find a way to delete the unnecessary data.

Thanks in advance!

ShosMeister · 04-03-2008, 05:20 AM

If you don't care about keeping the existing data, you could create a formula to capture just the left 6 characters then copy/paste-special(values only) back over the existing data.

Alternatively, if you want to keep the existing data available, you could put the above forumula (left(cell,6)) into a column inserted next to the existing data. Then, just hide the existing column and only display the forumula column.

Need2BAGeek · 04-03-2008, 09:27 AM

Thanks! I'm not experienced with formulas, so I didn't know they were capable of doing this. I'll dig into it and see what I find. I'll post the exact formula once I have it.

Jolt · 04-03-2008, 09:30 AM

The data that is exported, is it in an excel format or a .txt format?

If it is the latter, simply open up notepad and then open the file and do a find and remove of the phrase you want to get rid of.

ShosMeister · 04-03-2008, 12:55 PM

Quote:

Originally Posted by Need2BAGeek

Thanks! I'm not experienced with formulas, so I didn't know they were capable of doing this. I'll dig into it and see what I find. I'll post the exact formula once I have it.

Sorry for not being able to provide more info, but, if you have any other questions, just stop by and we'll get you a syntactically correct forumula.

Need2BAGeek · 04-03-2008, 04:52 PM

Thanks to everyone for their advice. While I was researching formulas I found a tool on the Data menu called "Text to Colums." With this tool I was able to set a line to divide the characters I wanted to keep in the column from the characters I wanted to delete. I then finished the "Text to Columns" wizard and it moved the characters I didn't want to keep to other columns to the right. I then deleted those extra columns that were created and everything is exactly how I want it.

Thanks again!

ShosMeister · 04-03-2008, 05:02 PM

Great!!! There is always more than one way to get things done. Glad you found one that worked for you!!!

04-02-2008, 05:19 PM	#1 (permalink)
Need2BAGeek Registered User Join Date: Nov 2005 Location: Illinois, US Posts: 36 OS: WinXP Pro SP2 & Fedora 8	[SOLVED] Excel question - deleting/hiding certain data in a column Before I explain my issue, my system is running WinXP Pro SP2 and I'm using Excel 2002. If you need more info, just ask. My issue is that I have a spreadsheet that was created by exporting data from a third-party application and one of the columns displays too much info. The column I'm referring to contains file names, which are in the format "##-### - <text>.bin". So all 5,000+ rows in this column have "##-### - <text>.bin" in it, but I need the column to only have the first 6 characters, "##-###". The first 6 characters are a unique identifier comprised of 2 digits, a hyphen, and 3 more digits. I'd like to delete/hide the remainder of the file name " - <text>.bin" so users aren't confused. Are there any functions in Excel that allow me to delete all data in this column except the first 6 characters of each cell? I can't find a way. Please let me know if anyone has any ideas. For printing purposes, I can change the column width to hide everything except the first 6 characters. But for the purpose of sharing this file with other users, I'd like to find a way to delete the unnecessary data. Thanks in advance!

04-03-2008, 05:20 AM	#2 (permalink)
ShosMeister Registered User Join Date: Jan 2008 Location: Harpers Ferry, WV Posts: 400 OS: OS X 10.4.x	Re: Excel question - deleting/hiding certain data in a column If you don't care about keeping the existing data, you could create a formula to capture just the left 6 characters then copy/paste-special(values only) back over the existing data. Alternatively, if you want to keep the existing data available, you could put the above forumula (left(cell,6)) into a column inserted next to the existing data. Then, just hide the existing column and only display the forumula column.

04-03-2008, 09:27 AM	#3 (permalink)
Need2BAGeek Registered User Join Date: Nov 2005 Location: Illinois, US Posts: 36 OS: WinXP Pro SP2 & Fedora 8	Re: Excel question - deleting/hiding certain data in a column Thanks! I'm not experienced with formulas, so I didn't know they were capable of doing this. I'll dig into it and see what I find. I'll post the exact formula once I have it.

04-03-2008, 09:30 AM	#4 (permalink)
Jolt Registered User Join Date: Apr 2008 Posts: 71 OS: Vista SP1, XP SP2 My System	Re: Excel question - deleting/hiding certain data in a column The data that is exported, is it in an excel format or a .txt format? If it is the latter, simply open up notepad and then open the file and do a find and remove of the phrase you want to get rid of.

04-03-2008, 04:52 PM	#6 (permalink)
Need2BAGeek Registered User Join Date: Nov 2005 Location: Illinois, US Posts: 36 OS: WinXP Pro SP2 & Fedora 8	Re: [SOLVED] Excel question - deleting/hiding certain data in a column Thanks to everyone for their advice. While I was researching formulas I found a tool on the Data menu called "Text to Colums." With this tool I was able to set a line to divide the characters I wanted to keep in the column from the characters I wanted to delete. I then finished the "Text to Columns" wizard and it moved the characters I didn't want to keep to other columns to the right. I then deleted those extra columns that were created and everything is exactly how I want it. Thanks again!

04-03-2008, 05:02 PM	#7 (permalink)
ShosMeister Registered User Join Date: Jan 2008 Location: Harpers Ferry, WV Posts: 400 OS: OS X 10.4.x	Re: [SOLVED] Excel question - deleting/hiding certain data in a column Great!!! There is always more than one way to get things done. Glad you found one that worked for you!!!