filter specific number in a text string

samshan143

New Member
Joined
Mar 12, 2013
Messages
23
Hi,
I want to filter a specific number in a series of column, presently I tried to filter using CONTAINS but it also shows result numbers prefixed or suffixed alongwith the filtered numbers. Let me explain with an example.

suppose i want to filter 403 in following column
abc 403 xyz
def 60403 hij
ccc 12345 ffg

<tbody>
</tbody>


in above if i want to filter only 403 and if i use filter using CONTAINS 403, then the result will show both the columns i.e. one with 403 and also 60403.

is there any way where if i use contain filter for 403 it should show filtered result of 403 only and NOT 60403 or like 4030 etc.

please assist.

regds
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi,
I want to filter a specific number in a series of column, presently I tried to filter using CONTAINS but it also shows result numbers prefixed or suffixed alongwith the filtered numbers. Let me explain with an example.

suppose i want to filter 403 in following column
abc 403 xyz
def 60403 hij
ccc 12345 ffg

<tbody>
</tbody>


in above if i want to filter only 403 and if i use filter using CONTAINS 403, then the result will show both the columns i.e. one with 403 and also 60403.

is there any way where if i use contain filter for 403 it should show filtered result of 403 only and NOT 60403 or like 4030 etc.

please assist.

regds

B2, copied down:
Rich (BB code):
=LOOKUP(9.99999999999999E+307,
  MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&"1234567890")),
  ROW(INDIRECT("1:"&LEN(A2))))+0)
 
Upvote 0
If you are only looking for specifically 403 as a stand alone and not in as a part of another group of characters what about filtering on " 403 " Note the space before and after 403.
 
Upvote 0
Dear Isemmens, i tried that, but it is showing same result as before... (yes i used spaces before and after 403)
any other way pls
 
Upvote 0
too complex for simple filter, i presume i have to add column for this right? pls is there any way using CONTAINS filter?
 
Upvote 0
Hi

The autofilter disregards the spaces before and after.

The way to force it to include in the string is to enclose it in asterisks.

Try contains

* 403 *

(asterisk space 403 space asterisk)
 
Last edited:
Upvote 0
Hi

The autofilter disregards the spaces before and after.

The way to force it to include in the string is to enclose it in asterisks.

Try contains

* 403 *

(asterisk space 403 space asterisk)

Hi,

I tried as you said, but still it does not give the desired result.

any other ways pls
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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