How to find invisible characters in Excel

Question

I have here an Excel file that checks the presence of:

long hyphen (–)
curly apostrophe (’)
ï (e.g., naïve)
Non-breaking hyphen (‑)

Screenshot 1

Screenshot 2

Cell formula after each header above from Q to T (6th row):

long hyphen (–)
=IFERROR(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ", TRUE, IF(ISNUMBER(FIND("–", A6:I1000)), CHAR(64+COLUMN(A6:I1000)) & ROW(A6:I1000), "")), ", ", , TRUE)), "No matches found")

curly apostrophe (’)
=IFERROR(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ", TRUE, IF(ISNUMBER(FIND("’", A6:I1000)), CHAR(64+COLUMN(A6:I1000)) & ROW(A6:I1000), "")), ", ", , TRUE)), "No matches found")

ï (e.g., naïve)
=IFERROR(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ", TRUE, IF(ISNUMBER(FIND("ï", A6:I1000)), CHAR(64+COLUMN(A6:I1000)) & ROW(A6:I1000), "")), ", ", , TRUE)), "No matches found")

Non-breaking hyphen (‑)
=IFERROR(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ", TRUE, IF(ISNUMBER(FIND("‑", A6:I1000)), CHAR(64+COLUMN(A6:I1000)) & ROW(A6:I1000), "")), ", ", , TRUE)), "No matches found")

Reset VBA code:

Sub ClearData()
    'ClearData
    Range("A6:L1000").Select
    Selection.ClearContents
    Range("A1").Select
End Sub

How to update formula above that could detect the invisible characters pointed in arrows below (after the text is pasted here, the first inv char "U+A0" is removed):

This is the text (same text in the Screenshot 1 above):

See what's hidden in your string…or behind ‑-–’

This is the online tool I usually use to check the invisible characters.

Based on Screenshot 1 above, expected result pointed in arrow below (a formula that identifies the cells that contain invisible character):

Ike · Accepted Answer · 2025-06-13 06:25:06Z

0

If you have a list of non-printable characters you could use a formula like this - it is placed in B4

=LET(TextToCheck,$D$3,invCharacter,A4,
    split,MID(TextToCheck,SEQUENCE(,LEN(TextToCheck)),1),
    unicodeCharacters,MAP(split,LAMBDA(c,UNICODE(c))),
    ISNUMBER(MATCH(invCharacter,unicodeCharacters,0)))

You can visualize each character and each unicode with following formulas:

D3:

=LET(TextToCheck,$D$3,    MID(TextToCheck,SEQUENCE(,LEN(TextToCheck)),1)
    )

D4:

=LET(TextToCheck,$D$3,
    split,MID(TextToCheck,SEQUENCE(,LEN(TextToCheck)),1),
    MAP(split,LAMBDA(c,UNICODE(c)))
    )

edited Jun 13 at 6:25

answered Jun 12 at 12:36

Ike

13.4k6 gold badges20 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shiela Jun 12 at 22:50

hi, added the text with inv chars in cell d3, added the formula in cell b4, b4 returned false.

Ike Jun 13 at 6:25

I added some intermediate steps - so you can check the text by each charachter - does this help?

Shiela Jun 13 at 11:21

ty. it shows all the characters. will there be a way that the formula will only show the cell number of the invisible one like in the last part of the post above?

Collectives™ on Stack Overflow

How to find invisible characters in Excel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related