Search for Substring "001" do not return 0"001" from 0001

[hodgts]

Hi,

Need to Search for "001" in a column that contains xxxx001 as well as xxxxx00001.
Variable length strings in the column.

Please advise on how Search would return xxxx001 not xxxxx00001.

Cheers,

Tim

 

[NARAYANK991]

Hi Tim ,

Suppose the data in a range in a particular column is as follows :

ABCD001
xdfg1234
dfgh0001
ABC0001
ABCDE00001
1234001

In your Find dialog , if you enter the string :

????001

and check the box labelled :

Match entire cell contents

then the returned data will be the following :

ABCD001
ABC0001
1234001

i.e. all cells which are 7 characters in length , and whose last 3 characters are 001.

Narayan

 

[Somendra Misra]

Hi Tim,

Another suggestion:

Put a helper column with below formula :

=MID(A2,FIND("001",A2)-1,1)

Assuming your first value is in A2 and copy down.

Then filter on this column and select all value except 0 and error values.

Regards,