|
Filtering Data
Dear All I have a problem could somebody please help? I have a list of data and I would like to filter it buy removing the first 4 characters DHK-6000 DHK-6000. DHK-6000HD DHK-6000HDB SZK-6001 SZK-6001. RVK-6002 RVK-6002. To make it look like this 6000 6000. 6000HD 6000HDB 6001 6001. 6002 6002. As I have thousands of these I do not want to do this manually Also once I have done this how do I filter the data so that it does not display any data containing a B. Answers to either question would be much appreciated. Thanks Andrew -- koba ------------------------------------------------------------------------ koba's Profile: http://www.excelforum.com/member.php...o&userid=28639 View this thread: http://www.excelforum.com/showthread...hreadid=483692 |
Filtering Data
koba Wrote: Dear All I have a problem could somebody please help? I have a list of data and I would like to filter it buy removing the first 4 characters DHK-6000 DHK-6000. DHK-6000HD DHK-6000HDB SZK-6001 SZK-6001. RVK-6002 RVK-6002. To make it look like this 6000 6000. 6000HD 6000HDB 6001 6001. 6002 6002. As I have thousands of these I do not want to do this manually Also once I have done this how do I filter the data so that it does not display any data containing a B. Answers to either question would be much appreciated. Thanks Andrew Hi Andrew To get rid of the four right hand characters, and assuming your data to be in column A, in Column B put this formula, =MID(A12,(SEARCH("-",A12)+1),7), where the last 7 is the maximum number of characters in any cell in column A To filter out those that contain be, do a custom filter, select does not contain and put B in the adjacent box -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=483692 |
Filtering Data
Found another way as well =LEFT(A2, LEN(A2)-2) Removes last two characters from contents of A2 =RIGHT(A3, LEN(A3)-8) Removes first 8 characters from A3 Thank heaps go team -- koba ------------------------------------------------------------------------ koba's Profile: http://www.excelforum.com/member.php...o&userid=28639 View this thread: http://www.excelforum.com/showthread...hreadid=483692 |
Filtering Data
one way:
=mid(a1,find("-",a1)+1, 256) HTH "koba" wrote: Dear All I have a problem could somebody please help? I have a list of data and I would like to filter it buy removing the first 4 characters DHK-6000 DHK-6000. DHK-6000HD DHK-6000HDB SZK-6001 SZK-6001. RVK-6002 RVK-6002. To make it look like this 6000 6000. 6000HD 6000HDB 6001 6001. 6002 6002. As I have thousands of these I do not want to do this manually Also once I have done this how do I filter the data so that it does not display any data containing a B. Answers to either question would be much appreciated. Thanks Andrew -- koba ------------------------------------------------------------------------ koba's Profile: http://www.excelforum.com/member.php...o&userid=28639 View this thread: http://www.excelforum.com/showthread...hreadid=483692 |
Filtering Data
If your data is in column A, starting in row 1, then in B2 enter the formula
=right(a2,len(a2)-4. Autofill that formula down through the entire column. If you only want to keep that trimmed value, you can copy/paste special values, then delete column A. Once that's done, click a single cell in your table and go to Data Filter Autofilter. Select the drop-down in the column with the trimmed data and choose 'Custom'. Use the first drop-down in that dialog to select 'Does Not Contain' and type B in the top-right text box. Hit OK. "koba" wrote: Dear All I have a problem could somebody please help? I have a list of data and I would like to filter it buy removing the first 4 characters DHK-6000 DHK-6000. DHK-6000HD DHK-6000HDB SZK-6001 SZK-6001. RVK-6002 RVK-6002. To make it look like this 6000 6000. 6000HD 6000HDB 6001 6001. 6002 6002. As I have thousands of these I do not want to do this manually Also once I have done this how do I filter the data so that it does not display any data containing a B. Answers to either question would be much appreciated. Thanks Andrew -- koba ------------------------------------------------------------------------ koba's Profile: http://www.excelforum.com/member.php...o&userid=28639 View this thread: http://www.excelforum.com/showthread...hreadid=483692 |
Filtering Data
sorry one other way ( i hope too many solutions do not confuse)
=MID(A1,5,LEN(A1)) copy this down. "bpeltzer" wrote in message ... If your data is in column A, starting in row 1, then in B2 enter the formula =right(a2,len(a2)-4. Autofill that formula down through the entire column. If you only want to keep that trimmed value, you can copy/paste special values, then delete column A. Once that's done, click a single cell in your table and go to Data Filter Autofilter. Select the drop-down in the column with the trimmed data and choose 'Custom'. Use the first drop-down in that dialog to select 'Does Not Contain' and type B in the top-right text box. Hit OK. "koba" wrote: Dear All I have a problem could somebody please help? I have a list of data and I would like to filter it buy removing the first 4 characters DHK-6000 DHK-6000. DHK-6000HD DHK-6000HDB SZK-6001 SZK-6001. RVK-6002 RVK-6002. To make it look like this 6000 6000. 6000HD 6000HDB 6001 6001. 6002 6002. As I have thousands of these I do not want to do this manually Also once I have done this how do I filter the data so that it does not display any data containing a "B". Answers to either question would be much appreciated. Thanks Andrew -- koba ------------------------------------------------------------------------ koba's Profile: http://www.excelforum.com/member.php...o&userid=28639 View this thread: http://www.excelforum.com/showthread...hreadid=483692 |
| All times are GMT +1. The time now is 03:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com