ISNUMBER(SEARCH( function to only find words beginning with

[ollyhughes1982]

Hi,

I have the formula (below) and wondered if there is a way to amend it so it only searches strings beginning with the words, rather than for the words being present anywhere within the string.

=SORT(FILTER('parkrun Reader Dump'!B3:B5003,('parkrun Reader Dump'!N3:N5003=1)*('parkrun Reader Dump'!O3:O5003=1)*('parkrun Reader Dump'!P3:P5003=1)*('parkrun Reader Dump'!D3:D5003="United Kingdom")*((ISNUMBER(SEARCH("Pont y",'parkrun Reader Dump'!B3:B5003)))+(ISNUMBER(SEARCH("Ponte",'parkrun Reader Dump'!B3:B5003)))+(ISNUMBER(SEARCH("Ponty",'parkrun Reader Dump'!B3:B5003))))))

Thanks,

Olly.

 

[Fluff]

Maybe

=SORT(FILTER('parkrun Reader Dump'!B3:B5003,('parkrun Reader Dump'!N3:N5003=1)*('parkrun Reader Dump'!O3:O5003=1)*('parkrun Reader Dump'!P3:P5003=1)*('parkrun Reader Dump'!D3:D5003="United Kingdom")*(LEFT('parkrun Reader Dump'!B3:B5003,4)="Pont")))

 

[ollyhughes1982]

Hi. Thanks. That does work at the moment, but if more events were added that are similar it could pickup incorrect ones. Would it be possible to have three separate LEFT functions, searching on Pont y, Ponte and Ponty?

I have tried to do this (below formula), but it doesn't work:

=SORT(FILTER('parkrun Reader Dump'!B3:B5003,('parkrun Reader Dump'!N3:N5003=1)*('parkrun Reader Dump'!O3:O5003=1)*('parkrun Reader Dump'!P3:P5003=1)*('parkrun Reader Dump'!D3:D5003="United Kingdom")*(LEFT('parkrun Reader Dump'!B3:B5003,4)="Pont y")))*(LEFT('parkrun Reader Dump'!B3:B5003,4)="Ponte")))*(LEFT('parkrun Reader Dump'!B3:B5003,4)="Ponty")))

Thanks again.

 

[Fluff]

It would need to be like

=SORT(FILTER('parkrun Reader Dump'!B3:B5003,('parkrun Reader Dump'!N3:N5003=1)*('parkrun Reader Dump'!O3:O5003=1)*('parkrun Reader Dump'!P3:P5003=1)*('parkrun Reader Dump'!D3:D5003="United Kingdom")*((LEFT('parkrun Reader Dump'!B3:B5003,6)="Pont y")+(LEFT('parkrun Reader Dump'!B3:B5003,5)="Ponte")+(LEFT('parkrun Reader Dump'!B3:B5003,5)="Ponty"))))

 

[ollyhughes1982]

Yes, that worked perfectly! Thanks.

 

[Fluff]

Glad to help & thanks for the feedback.