Filtering Data

[koba]

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

 

[Paul Sheppard]

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

 

[koba]

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

 

[Guest]

one way:
=mid(a1,find("-",a1)+1, 256)
HTH

 

[Guest]

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.

 

[R.VENKATARAMAN]

sorry one other way ( i hope too many solutions do not confuse)
=MID(A1,5,LEN(A1))
copy this down.