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]

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

For starters, you're looking into column C instead of B.

 

[ollyhughes1982]

For starters, you're looking into column C instead of B.

Column C is correct, it's column C of another worksheet. The page works perfectly except for the one unusual character, it's 'seeing' it as a normal 'W'.

 

[Cubist]

Do you want all results that contain the character or just the first one?

 

[ollyhughes1982]

Exactly the same as the other ones; only the first time I ever completed a run where the run name contains that character. I was wondering whether there might be a solution to perhaps add the 'CODE' function into the formula so that it converts the A48 value within the formula and it will then find the correct one? Rather than returning where it finds 'W'.

 

[Cubist]

Ok, try

Book1
	A	B	C	D
1	This is a special character ʷ	ʷ		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	parkrun Las Miejski	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,"")

 

[Micron]

If it is not a normal character I don't see how you'll find it that way (but happy to be proved wrong).
Try this in the immediate window of the vba editor: ?ascw(sheets("D").range("L10"))
Change your sheet and range references to suit. If you get a number higher than 255, it's probably a Unicode character. I imagine you could find such characters with a vba procedure if you can't get a formula to work.

 

[ollyhughes1982]

Ok, try

Book1
	A	B	C	D
1	This is a special character ʷ	ʷ		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	parkrun Las Miejski	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,"")

Not sure how I would put this into cell B48. The current formula works in them all, except it just doesn't recognise the special character. Can I just add part of your into mine to get it to pick up the character? What did you do to make it recognise that character?

 

[Cubist]

Replace your current formula with this.

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

 

[ollyhughes1982]

Replace your current formula with this.

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

Will give that a try now. Will this work for the others as well, or shall I leave the same. I'd rather have the same format of formula in all rows, if possible