Find Hidden Characters

I'm not up on power query but just to ask, why do you think there are hidden figures in cells A2 through A5 or is there a different area you are looking at?
for example, =LEN(A2) (same for A3) returns 31 which is the count of the letters and spaces within the text Ally/Supporter of Self-Advocate.
=LEN(A4) and A5 both return 30 also the count of letters and spaces within the text Family Member of Self-Advocate.

Here's how I would do this:

1) In C2, enter =LEN(A2) just to know how long each text string is.
2) In D1:AZ1 (or however far to the right I need for the longest text string I will ever need), enter the numbers 1 to 49.
3) In D2, enter =CODE(MID($A2,D$1,1)). Note the mix of relative and absolute references, then copy/paste/fill into D2:AZ5.

Now I have rows that show the ASCII code number for each character in each string, and I can compare these numbers.

The thing I notice is that rows 2 and 4 are using code 32 space characters for the spaces in the text string. Rows 3 and 5, on the other hand, are using code 160 space characters. I haven't looked at each string thoroughly, but that appears to be one common difference between what otherwise should be equivalent text strings.

What to do next depends on exactly how you want to approach the discrepancy, but I would expect some kind of Find/Replace task or REPLACE() or SUBSTITUTE() functions or equivalent that would "clean up" these kind of "same but different" characters.

The characters that look like spaces in the second version of the strings are actually non-breaking spaces. A regular space is code 32 and your spaces are code 160.

You can fix this by a single Find & Replace All. In the Find box hold the ALT key and type 0160 on the numeric keypad. In the Replace box just type a space.

Yes...

=TRANSPOSE(CODE(MID(Table1[@[What is your role?]],SEQUENCE(LEN(Table1[@[What is your role?]])),1)))

tells you what characters are in each string. You can see (yellow) where Char 160 replaces space (Char 32).

You can use this instead of countif:

=SUMPRODUCT(--(SUBSTITUTE([What is your role?],CHAR(160)," ")=SUBSTITUTE([@[What is your role?]],CHAR(160)," ")))

LoL... No idea at all. I never even attempted to learn PQ.

A quick internet search found this help page: https://learn.microsoft.com/en-us/po...m/text-replace

MrS... not for me, I'm wayyy to long in the tooth for learning something entirely new.

wow, looking in on this again this isn't a direction I thought this was going... interesting (I know, at least one of you is probably saying to yourself, yeah we know).

maybe

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

CHAR(160) <=> #(00A0) (non-breaking space)