filter specific number in a text string

[samshan143]

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

 

[Aladin Akyurek]

Hi,

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

any other ways pls

Any reason why you don't want to use the formula approach?

A2: abc 403 xyz

B2:=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)

https://dl.dropboxusercontent.com/u...cific number in a text string samshan143.xlsx

 

[samshan143]

Any reason why you don't want to use the formula approach?

A2: abc 403 xyz

B2:=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)

https://dl.dropboxusercontent.com/u/65698317/aaFilter%20specific%20number%20in%20a%20text%20string%20samshan143.xlsx

as i said earlier, it is too complex formula to use in different sheets for similar requirements (i.e. like filtering 403 or any other number in text strings), so i prefer to keep it simple.


thanks for your understanding and any help is appreciated...

 

[Aladin Akyurek]

as i said earlier, it is too complex formula to use in different sheets for similar requirements (i.e. like filtering 403 or any other number in text strings), so i prefer to keep it simple.


thanks for your understanding and any help is appreciated...

You are welcome. And good luck.

 

[Rick Rothstein]

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

Based on everything you have told us so far, it looks like PGC's method should have worked. Why don't you help us to help you... don't just say "it doesn't work" (that tells us absolutely nothing useful)... tell us in what way it doesn't work so we have some idea where to look next.

 

[Aladin Akyurek]

Help us to help you... don't just say "it doesn't work" (that tells us absolutely nothing useful)... tell us in what way it doesn't work so we have some idea where to look next.

Different sheets as stipulated in the last reply to me, all manually? And not just one number, many more? May be you should give a macro!...

 

[István Hirsch]

If there is not simpler way available, I would replace spaces with # (for example), then filter for #403#, then replace # with space.

 

[István Hirsch]

If there is not simpler way available, I would replace spaces with # (for example), then filter for #403#, then replace # with space.

..if 403 is not in front or end position, as in the examples.

 

[markmzz]

Try this:

Advanced Filter

Data

CalcCriteria

Lookup Value

Data

Filter

Data

Helper Column

Lookup Value

abc 403 xyz

VERDADEIRO

403

abc 403 xyz

abc 403 xyz

VERDADEIRO

=ISNUMBER(SEARCH(" "&$N$2&" "," "&K2&" "))

403

def 60403 hij

=ISNUMBER(SEARCH(" "&$F$2&" "," "&B2&" "))

ab 403 xyz

def 60403 hij

FALSO

ccc 12345 ffg

403 hij

ccc 12345 ffg

FALSO

ab 403 xyz

ccc 403

ab 403 xyz

VERDADEIRO

403 hij

403 hij

VERDADEIRO

ccc 403

ccc 403

VERDADEIRO

4032 eettws

4032 eettws

FALSO

Result

**********

************

**

*******************************************

**

*******

**

**************

**

******

************

***************

*******************************************

*******

<tbody>
</tbody>

PS: VERDADEIRO is TRUE and FALSO is FALSE.

Or Advanced Filter with Calculated Criteria or Filter with a helper column.

I hope that the table above helps.

By the way, Pgc01's suggestion work for me too.

Markmzz

 

[István Hirsch]

Moreover, if 403 is not in front or end position, you can filter in a single step, using "? 403 ?" (without quotes, note the spaces next to the question marks).

 

[Rick Rothstein]

Different sheets as stipulated in the last reply to me, all manually? And not just one number, many more? May be you should give a macro!...

I would consider writing such a macro... if I understood exactly what the OP wanted. I am not sure if the number he wants to filter on can be at the beginning or end of the text... or if the spaces are real spaces... or whether other punctuations can be next to the number (for example, is the 403 in ABC(403)DEF considered standalone or not?)... etc.