Identify variable within comma separated list, without wildcards (Chester <> Chesterfield / Manchester)

[RockandGrohl]

Hi all,

Got a quandary here. I'm taking a customer pickup as variable, using Chester as an example, and searching for a match within a comma separated list of pickups which may look like this:

Chichester, Bognor Regis, Littlehampton, Worthing, Shoreham-by-Sea, Hove, Brighton, Horsham, Pease Pottage Services (M23), Crawley, Horley

I'm searching a large set of data using filters and holding "Chester" as the variable, unfortunately it will show a match for any string that contains *Chester* - including Chichester, Manchester, Chesterfield etc

Any ideas how to search for -just- Chester? In the past I've tried searching for "* Chester*" but obviously this would still count Chesterfield, so you'd think "* [Pickup],*" would work, but this would not work when the pickup needed is at the start or end of the string.

Thank you!

 

[tyija1995]

Hey, try this:

NCT_Mapping_Doc.xlsx
	A	B
1	Manchester is big
2	Chesterfield is small
3	Chester is ok	Flag
4	is chester small?	Flag
5	is it chester	Flag
Sheet6

Cell Formulas
Range	Formula
B1:B5	B1	=IF(IFERROR(SEARCH(" chester "," "&A1&" "),0)>0,"Flag","")

 

[Fluff]

Assuming that this is VBA, can you please post the relevant code?

 

[RockandGrohl]

Assuming that this is VBA, can you please post the relevant code?

Range("A2:AF" & pplastrow).AutoFilter Field:=11, Criteria1:="=*" & pup & "*"

Essentially this is how the filter operates. Cheers.

 

[Fluff]

If you have a load of comma separated string in col K, then you will not be able to use AutoFilter to just show rows that contain Chester, but not Manchester etc.