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

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

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

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

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

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