FIlter Data

bala06

New Member
Joined
May 31, 2011
Messages
39
Dear Excel Users

I would like to filter the filter the data according to certain numbers.

For example my data looks like this

1049 1049-hydrogen-bond-frame.dat.c.d.lw 1049-hydrogen-bond-frame.dat.c.d.pw [(34652 'SPC' 'X2591G' 4758 'UNK' 'Z1203B' 34653 'SPC' 'X2591G' 3895 'LYS' 'A 347B') (57086 'SPC' 'X2755G' 4733 'UNK' 'Z1203B' 57087 'SPC' 'X2755G' 3895 'LYS' 'A 347B')]
1054 1054-hydrogen-bond-frame.dat.c.d.lw 1054-hydrogen-bond-frame.dat.c.d.pw [(34652 'SPC' 'X2591G' 4758 'UNK' 'Z1203B' 34653 'SPC' 'X2591G' 3895 'LYS' 'A 347B')]
1055 1055-hydrogen-bond-frame.dat.c.d.lw 1055-hydrogen-bond-frame.dat.c.d.pw [(34652 'SPC' 'X2591G' 4758 'UNK' 'Z1203B' 34653 'SPC' 'X2591G' 3895 'LYS' 'A 347B') (4715 'UNK' 'Z1203B' 34653 'SPC' 'X2591G' 34653 'SPC' 'X2591G' 3895 'LYS' 'A 347B') (57086 'SPC' 'X2755G' 4733 'UNK' 'Z1203B' 57087 'SPC' 'X2755G' 3895 'LYS' 'A 347B') (57086 'SPC' 'X2755G' 4733 'UNK' 'Z1203B' 57087 'SPC' 'X2755G' 3947 'HIS' 'A 350B')]

I have to filter the data based on two numbers "4758 and 3895" and my final output should like this

1049 - 4758 3895
1054 - 4758 3895
1055 - 4758 3895

But the in my data, this numbers varies at different position according to length of the data.

Kindly advice.

Many Thanks
Balaji
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
If that helps
B2
:=LEFT(A2,FIND("-",A2)-1)

C1:=IF(AND(ISNUMBER(FIND("4758",A2)),ISNUMBER(FIND("3895",A2))),"4758 3895","")

Copy both down.
Then you can filter bu column C.
This will work with parameters you have supplied.
 
Upvote 0
If that helps
B2
:=LEFT(A2,FIND("-",A2)-1)

C1:=IF(AND(ISNUMBER(FIND("4758",A2)),ISNUMBER(FIND("3895",A2))),"4758 3895","")

Copy both down.
Then you can filter bu column C.
This will work with parameters you have supplied.

Dear Robert

It works.

Many Thanks
Balaji
 
Upvote 0
Dear Robert

It works.

Many Thanks
Balaji

Dear Robert

The formula works, but the numbers that are mapping in the formula should be in same brackets.

If the two numbers are present in separete brackets, i would like to eliminate those numbers.

Please advice.

Many Thanks
Balaji
 
Upvote 0
So in your second example(1054) the formula shoudl not return any value?
And the results will be:

1049 - 4758 3895
1055 - 4758 3895
 
Upvote 0
So in your second example(1054) the formula shoudl not return any value?
And the results will be:

1049 - 4758 3895
1055 - 4758 3895


Dear Robert

Yes, thats correct.

Sorry I havn't noticed carefully, while posting the thread.

Many Thanks
Balaji
 
Upvote 0
Try this(but it will work if those two numbers are in first brackets)

=IF(AND(ISNUMBER(FIND("4758",LEFT(SUBSTITUTE(A2,"(","@",2),FIND("@",SUBSTITUTE(A2,"(","@",2))))),ISNUMBER(FIND("3895",LEFT(SUBSTITUTE(A2,"(","@",2),FIND("@",SUBSTITUTE(A2,"(","@",2)))))),"4758 3895","")

Will you have any other posiblities?
 
Upvote 0
Try this(but it will work if those two numbers are in first brackets)

=IF(AND(ISNUMBER(FIND("4758",LEFT(SUBSTITUTE(A2,"(","@",2),FIND("@",SUBSTITUTE(A2,"(","@",2))))),ISNUMBER(FIND("3895",LEFT(SUBSTITUTE(A2,"(","@",2),FIND("@",SUBSTITUTE(A2,"(","@",2)))))),"4758 3895","")

Will you have any other posiblities?

Dear Robert

Thanks for prompt response.

But my data varies with length of the text and the corresponding numbers may present in 3rd or 4th brackets also.

I don't know, may macros script might help in this not sure (i'm not in familiar in macros ).

Kindly advice.

Many Thanks
Balaji
 
Upvote 0

Forum statistics

Threads
1,224,606
Messages
6,179,866
Members
452,948
Latest member
UsmanAli786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top