Why LEN seems wrong sometimes

A0041Unicode from zero

Computers can only store numbers. Text works because everyone agrees on which number stands for which character.

That agreement is called a character set. One of the earliest was ASCII (1963). It covered 128 characters: English letters, digits and punctuation. In ASCII, A is 65, B is 66 and a space is 32. That was enough for English and nothing else. There was no é, no Ω and no ☂.

Other countries built their own sets, and they reused the same numbers for different characters. A file written with one set and opened with another came out as garbage, which is why you sometimes still see café where café should be.

Unicode (1991) fixed this by giving every character in every writing system its own number, plus symbols, math and eventually emoji. That number is the character’s code point.

Reading U+ notation

Code points are written as U+ followed by the number in hexadecimal (base 16). Hex uses sixteen digits, 0–9 and then A–F, so A means 10 and F means 15.

Why hex and not ordinary decimal? Each hex digit fits exactly into 4 bits, so hex lines up neatly with how computers store data. Four hex digits are exactly 16 bits. That fact explains the LEN puzzle, so keep it in mind.

Seeing code points in Excel

Two functions convert between characters and their code points, and two more convert between decimal and hex:

=UNICODE("A")            → 65       character → code point (decimal)
=UNICHAR(9730)           → ☂        code point → character
=DEC2HEX(9730)           → 2602     decimal → hex, so this is U+2602
=UNICHAR(HEX2DEC("1F468")) → 👨      from U+ notation back to the character

👨1F468Why LEN says 8 (or 5)

Back to the family emoji. We’ll count it three times, looking a little deeper into how it’s stored each time. Both of Excel’s answers will turn up along the way.

Neither answer is wrong. They answer different questions. In Version 1, LEN answers “how many 16-bit slots does this take up?” and says 8. In Version 2, it answers “how many Unicode characters is this?” and says 5. Neither counts what you see. The split into two slots is called a surrogate pair (section 5). Which count your workbook uses depends on its compatibility version (section 4).

Check each stage yourself:

Open the family in the workbench

é00E9Three meanings of “character”

The three counts you just saw have names. Most text bugs come from counting one of them while thinking about another.

Here are more examples. Each one is a single grapheme, and they don’t all look like emoji:

On Windows the flag shows as the letters “JP”, because Windows doesn’t include flag emoji. The data is fine; it’s still one grapheme.

🐸1F438Compatibility versions

For decades, LEN("😀") returned 2. Microsoft has now fixed that. To avoid silently changing the results of millions of existing spreadsheets, the fix is a per-workbook setting.

A compatibility version is a workbook setting that decides which behaviour certain functions use. There are currently two:

What changes

Microsoft lists exactly five functions: LEN, MID, FIND, SEARCH and REPLACE. In Version 2 they treat a surrogate pair as one character, so lengths and positions are counted in code points.

What doesn’t change

• Version 2 still doesn’t count graphemes. It counts code points. Microsoft specifically notes that variation selectors are still counted separately, and so are skin tones and ZWJ joiners, which is why the family is still 5. For a grapheme count, see section 7.
• Other text functions aren’t on the list. Coverage of the change describes LEFT, RIGHT, TEXTBEFORE, TEXTAFTER and TEXTSPLIT as already handling surrogate pairs correctly. Comparisons (=, EXACT) and SUBSTITUTE aren’t mentioned either, so section 10 applies in both versions.

Checking and changing it

Go to Formulas › Calculation Options › Compatibility Version. The setting belongs to the workbook, not to your copy of Excel. Two workbooks open side by side can give different LEN results for the same text.

There’s no function that reports the version directly, but the frog gives it away. A single emoji is 2 slots in Version 1 and 1 code point in Version 2:

=IF(LEN(UNICHAR(128056)) = 1, "Version 2", "Version 1")

The rollout details are recent and may change. Microsoft’s Compatibility Versions page has the latest.

HID83DSurrogate pairs

This section explains how a code point too big for one slot gets split into two. It’s the reason for Version 1’s 8, and the thing Version 2 was built to hide.

A little history

Early Unicode assumed 65,536 characters (0000–FFFF) would be enough for everything. Windows, Java, JavaScript and Excel were designed around that, with one 16-bit slot per character. In 1996 Unicode grew past that limit to make room for historic scripts, rare Chinese characters and, later, emoji. The new maximum was 10FFFF.

Those systems couldn’t switch to bigger slots without breaking everything already written. So Unicode set aside 2,048 values in the 16-bit range that aren’t characters at all. Each one means “I’m half of a bigger number.” These are surrogates:

• A high surrogate (D800–DBFF) always comes first and carries the top half of the number.
• A low surrogate (DC00–DFFF) always comes second and carries the bottom half.

Together they make a surrogate pair: two slots that represent one code point. The two ranges don’t overlap, so any slot tells you whether it’s a first half, a second half, or a normal character.

The code space is divided into 17 planes of 65,536 code points each. Plane 0, the Basic Multilingual Plane, covers everything up to FFFF. That includes almost all everyday text in most languages, and it fits in one slot. Planes 1–16, sometimes called the “astral” planes, need a pair. Most emoji live in plane 1, which is why a single emoji has a LEN of 2 in Version 1 workbooks.

Worked example: 🐸 U+1F438 frog face

1F438 is bigger than FFFF, so it needs two slots. The recipe is: subtract 10000, split what’s left into two 10-bit halves, and add each half to the start of its surrogate range.

cp = (hi − 0xD800) × 0x400 + (lo − 0xDC00) + 0x10000
   = (0xD83D − 0xD800) × 0x400 + (0xDC38 − 0xDC00) + 0x10000
   = 0x3D × 0x400 + 0x38 + 0x10000
   = 0x1F438  ✓

It’s still one code point. The pair only exists in UTF-16. Other encodings store the same frog differently:

Open the frog in the workbench Open a lone high surrogate

◌́0301Grapheme clusters

A grapheme is what a reader sees as one character. Unicode’s name for it is an extended grapheme cluster: a base character plus everything attached to it.

Some code points never stand alone. They modify the character in front of them, like an accent placed on a letter. When text is split into graphemes, these attaching characters stay with the base:

Unicode doesn’t keep a list of every possible grapheme, because there are far too many combinations. Instead it gives every code point a property that says how it behaves: “I’m a normal character,” “I attach to the previous one,” “I’m a joiner,” “I’m half a flag,” and so on. A short set of rules in a document called UAX #29 uses those properties to decide where one grapheme ends and the next begins. In short:

• Accents and modifiers attach. Combining marks, skin tones and the invisible emoji-style marker U+FE0F stay with the character before them.
• Joined emoji stay together. Emoji + ZWJ + emoji forms one grapheme.
• Flag letters pair up. Regional-indicator letters join two at a time, so 🇯🇵🇫🇷 is two flags (Japan and France) and not one four-letter blob.
• Everything else is its own grapheme.

No version of LEN follows these rules. Excel does have a way to split text into graphemes, though, and it’s covered in the next section.

X0058Counting graphemes with \X

Excel for Microsoft 365 has three regular expression functions: REGEXTEST, REGEXEXTRACT and REGEXREPLACE. Their regex language includes a pattern that matches exactly one grapheme.

A regular expression (regex) is a small pattern language for describing text. Excel’s regex functions use a variety called PCRE2, where \X (capital X) means “one extended grapheme cluster.” That’s the same unit the grapheme rules in section 6 produce: what a reader sees as one character.

REGEXEXTRACT(text, pattern, [return_mode]) returns the text that matches the pattern. With return_mode set to 1 it returns every match as an array that spills into neighbouring cells. Using \X as the pattern returns one cell per grapheme:

A1:  👨‍👩‍👧🐸é

=REGEXEXTRACT(A1, "\X", 1)        → 👨‍👩‍👧 | 🐸 | é     (spills 3 cells)

Now you can do what LEN never could:

• The IF guard. When nothing matches (an empty cell), REGEXEXTRACT returns an error, and COUNTA would count that error as 1.
• TOCOL makes the spill a single column whichever direction it comes out, so TAKE always takes the first n items.
• "(?s).": a . matches one code point, never half a surrogate pair, so this lists code points the same way in Version 1 and Version 2. (?s) lets . match line breaks too.
• No escaping needed. Backslashes are ordinary characters in Excel strings, so type "\X" exactly as shown. Only double quotes need doubling.

The family emoji, all three ways:

Regex is also a compact way to delete invisible characters. This removes all 16 variation selectors in one go:

=REGEXREPLACE(A1, "[\x{FE00}-\x{FE0F}]", "")

\x{FE0F} is PCRE2’s way to write a code point in hex, and […-…] matches anything in that range.

ZWJ200DThe zero-width joiner U+200D

The ZWJ is the invisible glue from the family emoji. On its own it has no width and no shape. Put it between two emoji and it asks the font to draw them as one combined picture.

The approved combinations are listed in a file called emoji-zwj-sequences.txt, and the set is called RGI (“recommended for general interchange”). If a combination isn’t on the list, or your font doesn’t have it, the pieces are drawn side by side.

There’s a wrinkle. The grapheme rules only look at properties (“this is an emoji, this is a ZWJ”) and never check the approved list. So the frog-umbrella counts as one grapheme in the workbench and with \X, even though you see two pictures. Most of the time, what you see and the grapheme count agree. When they don’t, the grapheme count follows the rules and the picture follows the font.

The ZWJ also explains a common cleanup bug. If you delete all the emoji from some text, the invisible ZWJs that sat between them are still there, making LEN higher than it should be.

VS16FE0FText vs emoji style

Many symbols can be drawn two ways: a plain black-and-white symbol (“text style”) or a colourful emoji (“emoji style”). It’s the same code point either way.

Each symbol has a default style. Older symbols from before emoji existed, like the umbrella, usually default to text style. Newer ones usually default to emoji style. To override the default, you add an invisible variation selector right after the symbol:

• U+FE0F (“VS16”) means draw it as an emoji.
• U+FE0E (“VS15”) means draw it as text.

The default is recorded in a Unicode property called Emoji_Presentation. Your screen may not match the table: many fonts, including Windows’ emoji font, draw some symbols in colour whatever the selector says. That makes the problem worse, because two strings with different code points end up looking identical.

Å212BNormalization: making equal text look equal

To normalize text is to rewrite it into one standard form, so that text that means the same thing is also stored the same way. Then even strict comparisons like EXACT agree.

There are two kinds of cleanup, and they need very different tools. Some you can work out from the code point’s number alone. The rest needs a lookup table.

Kind 1: the number tells you (a formula can do it)

A few kinds of character can be spotted by their number because they sit in known ranges. For comparison purposes you can usually just delete them:

With the regex functions it’s one line, because a character class can hold several ranges:

=REGEXREPLACE(A1, "[\x{FE00}-\x{FE0F}\x{1F3FB}-\x{1F3FF}\x{200D}]", "")

Without them (any Excel 365), loop over the list of numbers and remove each character in turn:

=LET(
  strip, VSTACK(SEQUENCE(16,,65024), 8205, SEQUENCE(5,,127995)),
  REDUCE(A1, strip, LAMBDA(s, c, SUBSTITUTE(s, UNICHAR(c), "")))
)

Save either one as a named LAMBDA (for example FOLDKEY) and compare FOLDKEY(A1) = FOLDKEY(B1). Use it only for comparing. Removing the ZWJ turns 👩‍💻 into 👩💻, which is not something you want to store.

Kind 2: needs a lookup table (a formula can’t)

Remember the two ways to write é? Unicode says they’re canonically equivalent, which means officially the same text:

• Composed: one code point, U+00E9 (233).
• Decomposed: two code points, U+0065 e (101) + U+0301 combining accent (769).

You can’t get from 233 to “101 + 769” with arithmetic. The pairing was simply decided by the Unicode Consortium and recorded in a table (UnicodeData.txt). Some pairings are even odder: Å U+212B ANGSTROM SIGN is officially the same as Å U+00C5. The standard forms have names:

• NFC (“composed”) turns everything into the one-code-point form where one exists. It’s the most common choice.
• NFD (“decomposed”) splits everything into base + accents.

One fun exception: the 11,172 Korean Hangul syllables are arithmetic. Each syllable’s number encodes its parts, and the workbench uses that to name them. Try 한 (U+D55C, “HAN”).

Where to do it instead

Normalize the text before it reaches the grid, or with a tool that has the table built in:

// JavaScript
"e\u0301".normalize("NFC") === "\u00E9"     // true

# Python
unicodedata.normalize("NFC", s)

' VBA: call the Windows API
Declare PtrSafe Function NormalizeString Lib "kernel32" ( _
    ByVal NormForm As Long, ByVal lpSrc As LongPtr, ByVal cwSrc As Long, _
    ByVal lpDst As LongPtr, ByVal cwDst As Long) As Long   ' NormForm 1 = NFC

Compare é with e + accent Compare the two Å

�FFFDHow many characters are there, really?

Unicode has room for 1,114,112 code points (17 planes × 65,536). Most of that room is empty.

So about 86% of the code space holds no standard character (1 − 154,998 / 1,114,112). If you don’t count the reserved areas (surrogates, private use and noncharacters), about 73% is still completely unassigned. There’s plenty of room for new emoji.

Reserved code points don’t have names. Tools show placeholders like <private-use-E000> instead.

The official character list, UnicodeData.txt, has far fewer lines than there are characters. Large blocks whose names follow a pattern are stored as just a first and a last line. For example, all 42,720 characters of CJK Extension B (20000–2A6DF) take two lines:

20000;<CJK Ideograph Extension B, First>;Lo;0;L;;;;;N;;;;;
2A6DF;<CJK Ideograph Extension B, Last>;Lo;0;L;;;;;N;;;;;