Searching for unusual characters

[ollyhughes1982]

Hi,

Re this strange character 'ʷ': Is there a way that you can do a search on it specifically? I have an alphabet-related parkrun challenge worksheet that looks for all letters within parkrun names. This strange character has appeared in one of the most recent new events. But, when I use my normal formula to search for this character, it is picking up the first event where the name included 'W' (Newport parkrun), rather than being blank, as I haven't yet completed the only event name that currently contains this character.

Is there perhaps a way to search on it's ASCII code or something, rather than the actually character, to prevent it looking for 'W' or 'w'?

The function that i have used up until now, that looks for the value in column A, is as follows:

=IFERROR(IF(A48<>"",XLOOKUP("*"&A48&"*",'All Completed Runs'!$C$4:$C$2003,'All Completed Runs'!$C$4:$C$2003,,2),""),"")

There is some background to this in two other previous threads:

List of unique characters from a list

List of unique characters from a list

Thanks in advance!

Olly.

 

[Cubist]

Should work for all.

 

[Fluff]

Unfortunately that will not work, it will look for a lower case w rather than the special character.

 

[ollyhughes1982]

It doesn't seem to work for me, it turns blank (see image).

Unfortunately that will not work, it will look for a lower case w rather than the special character.

Ah ok. It’s a strange one, as when I’ve been doing some testing it brings back an underscore character, instead of the strange one, if I use CHAR to switch it back after using CODE to turn it into a number

 

[Cubist]

It works for me. Are you positive that your column C contains that character?

Book1
	A	B	C	D
1	W w Lots of W'ss	ʷ		This is a special character ʷ
2	Strathaven junior parkrun	-
3	Ōrākei Bay parkrun	J		Jan Alfano Trail parkrun
4	Veterans Park parkrun	U
5	Otago Central Rail Trail parkrun, Alexandra	.
6	The Fisherman‘s Walk parkrun	#
7	This is a special character ʷ	1
8	Holmebrook Valley junior parkrun	2
9	Fulwell Quarry parkrun	3
10	Morden parkrun	4
11	Lake Pacawa parkrun	ø
12	Halsema Trailhead parkrun	ð
13	Battersea junior parkrun	á
14	Athlone RSC parkrun	ń
15	Emerald Resort parkrun	Ł
16	Jan Alfano Trail parkrun	ó
17	Terra Nova parkrun	ź
18	Bay East Garden parkrun	ł
19	Y Promenâd parkrun	Ż
20	Tollgate Farm Park parkrun, baqʷab	q		Tollgate Farm Park parkrun, baqʷab
Sheet3

Cell Formulas
Range	Formula
D1:D20	D1	=XLOOKUP(TRUE,ISNUMBER(FIND(B1,$A$1:$A$20)),$A$1:$A$20,"")

 

[Fluff]

Apologies, your right it does work using FIND, but I suspect that D3 should return "Strathaven junior parkrun" as its got the first j

 

[Cubist]

@Fluff
I'm unclear whether the OP counts j vs. J as 2 separate unique characters. If not, we can convert all into upper or lower case.

 

[ollyhughes1982]

It works for me. Are you positive that your column C contains that character?

Book1
	A	B	C	D
1	W w Lots of W'ss	ʷ		This is a special character ʷ
2	Strathaven junior parkrun	-
3	Ōrākei Bay parkrun	J		Jan Alfano Trail parkrun
4	Veterans Park parkrun	U
5	Otago Central Rail Trail parkrun, Alexandra	.
6	The Fisherman‘s Walk parkrun	#
7	This is a special character ʷ	1
8	Holmebrook Valley junior parkrun	2
9	Fulwell Quarry parkrun	3
10	Morden parkrun	4
11	Lake Pacawa parkrun	ø
12	Halsema Trailhead parkrun	ð
13	Battersea junior parkrun	á
14	Athlone RSC parkrun	ń
15	Emerald Resort parkrun	Ł
16	Jan Alfano Trail parkrun	ó
17	Terra Nova parkrun	ź
18	Bay East Garden parkrun	ł
19	Y Promenâd parkrun	Ż
20	Tollgate Farm Park parkrun, baqʷab	q		Tollgate Farm Park parkrun, baqʷab
Sheet3

Cell Formulas
Range	Formula
D1:D20	D1	=XLOOKUP(TRUE,ISNUMBER(FIND(B1,$A$1:$A$20)),$A$1:$A$20,"")

Apologies, yes it does work, as I had stupidly forgotten to paste the name of the run with the unusual character into my test data. it does now pick it up. I will test, re @Fluff's comment, that the others still give the correct results.

 

[ollyhughes1982]

Apologies, yes it does work, as I had stupidly forgotten to paste the name of the run with the unusual character into my test data. it does now pick it up.

@Fluff
I'm unclear whether the OP counts j vs. J as 2 separate unique characters. If not, we can convert all into upper or lower case.

It doesn't work for all though, it brings back Riverfront as the first containing 'R' / 'r', but Newport is the first to contain 'R' / 'r', as this was my first event. Is it it picking up up the first event where there was a capitalised 'R', which Riverfront indeed is?

 

[ollyhughes1982]

Apologies, for this teaser. @Fluff is long used to my difficult conundrums!

 

[Cubist]

FIND is case-sensitive. I wasn't sure if you're counting lowercase r and upper case R as one unique character. If you're counting them as one then try.

=XLOOKUP(TRUE,ISNUMBER(FIND(UPPER(A48),UPPER('All Completed Runs'!$C$4:$C$2003))),'All Completed Runs'!$C$4:$C$2003,"")