How to Count Emojis in an Excel String (Step-by-Step)

If you have a column of text containing emojis, Excel can make the counting process a little confusing. The main reason is that an emoji is not always stored as one simple character, so LEN() may not always give you the number of emojis you see on screen.

The easiest method depends on what you actually need: the total length of the text, the number of a particular emoji, or the number of emoji characters in a mixed text string. Once you understand that difference, counting emojis in Excel becomes much easier.

What Does “Count Emojis” Mean in Excel?

Counting emojis in an Excel string means determining how many emoji symbols appear inside a cell’s text, rather than simply counting every character.

For example, suppose cell A2 contains:

Great job! 🎉🔥👏

Visually, you can see three emojis. But Excel’s character-counting behavior depends on the version of Excel and the Unicode structure of those emojis.

This matters when you’re working with social media captions, customer comments, product descriptions, survey responses, SMS copy, or any other text where emojis are mixed with ordinary letters.

Microsoft Excel’s LEN function returns the number of characters in a text string, but its handling of Unicode surrogate pairs has changed in newer Microsoft 365 compatibility versions. In Compatibility Version 2, many emojis that previously counted as two characters are counted as one. Some emoji components, such as variation selectors, can still be counted separately.

So if your goal is specifically “How many emojis are in this cell?”, LEN() alone is not enough.

First, Check the Total Length With LEN

If you only want to know how long the complete Excel string is, start with LEN.

Suppose A2 contains:

I love Excel 😊

Use:

=LEN(A2)

The result represents the length of the text according to the workbook’s Excel compatibility behavior.

This is useful when you’re checking whether a caption, message, or description fits within a particular character limit.

For example, if you’re preparing a short social media caption, you might first use:

=LEN(A2)

to see the overall length.

Why LEN Is Not an Emoji Counter

Imagine A2 contains:

Hello 😊🎉

You don’t really want to know the total number of characters. You want to know that there are two emojis.

LEN(A2) cannot make that distinction. It counts the text string rather than identifying which characters belong to the emoji category.

There is another complication: some emojis are constructed from multiple Unicode components. A family emoji, for example, can be made from several individual Unicode elements joined together. A skin-tone emoji can also contain more than one component.

That’s why “one emoji = one Unicode code point” isn’t always a safe assumption.

How to Count One Specific Emoji in Excel

If you only need to count a particular emoji, Excel has a much simpler solution.

Suppose A2 contains:

Great work 👍 👍 Keep going 👍

and you want to count the thumbs-up emoji.

Use:

=(LEN(A2)-LEN(SUBSTITUTE(A2,"👍","")))/LEN("👍")

This works by comparing the original text length with the length after every 👍 has been removed.

How the Formula Works

The formula has three main parts:

LEN(A2)

counts the original string.

Then:

SUBSTITUTE(A2,"👍","")

removes every thumbs-up emoji.

Finally, Excel compares the two lengths:

LEN(A2)-LEN(SUBSTITUTE(A2,"👍",""))

The difference represents the amount of text occupied by the removed emoji characters. Dividing by:

LEN("👍")

converts that difference into the number of thumbs-up emojis.

For a string containing three 👍 emojis, the formula returns:

3

This technique is particularly useful when you’re tracking a specific reaction emoji in survey responses or customer feedback.

Example: Counting Emojis in Customer Comments

Imagine you have customer comments in column A:

If you’re specifically tracking 😊, you can put this formula in B2:

=(LEN(A2)-LEN(SUBSTITUTE(A2,"😊","")))/LEN("😊")

Then drag the formula down the column.

Each row will show how many smiling-face emojis occur in that customer’s comment.

This is often more useful than trying to identify every possible emoji. If your analysis is about customer reactions, you may care about a handful of specific symbols such as 😊, 😍, 👍, ❤️, or 😡.

Note: Also use this tool for generating Random Wheel of Names !

How to Count Multiple Known Emojis

If you have a defined list of emojis that you want to count, you can count each one separately.

For example, suppose you want to count:

• 😊
• 😂
• ❤️
• 👍
• 🎉

You can create separate columns for each emoji.

For 😊:

=(LEN($A2)-LEN(SUBSTITUTE($A2,"😊","")))/LEN("😊")

For 👍:

=(LEN($A2)-LEN(SUBSTITUTE($A2,"👍","")))/LEN("👍")

For 🎉:

=(LEN($A2)-LEN(SUBSTITUTE($A2,"🎉","")))/LEN("🎉")

This gives you a small emoji-analysis table.

For example:

You can then use ordinary Excel functions such as SUM to calculate totals.

How to Count All Emojis, Not Just One

This is where the task becomes more complicated.

Excel does not have a simple built-in function called COUNTemoji() that identifies every Unicode emoji automatically.

You therefore need to decide what you mean by “emoji.”

One approach is to use a predefined list of emojis and compare the cell against that list. Another approach is to use Unicode ranges or regular expressions in Microsoft 365, but emoji detection is more complicated than simply checking whether a character falls inside one range.

Modern Microsoft 365 includes REGEXREPLACE and related regular-expression functions, which can be useful for text-processing tasks. Microsoft documents REGEXREPLACE as a Microsoft 365 function using the PCRE2 regular-expression flavor.

However, I would not recommend treating one huge Unicode range as a perfect “all emojis” detector. Emoji sequences can include variation selectors, zero-width joiners, skin-tone modifiers, flags, and other components.

If accuracy matters, a maintained emoji list is usually easier to control and audit.

A Useful Microsoft 365 Approach With an Emoji List

If you’re using Microsoft 365 and already have a list of emojis you care about, you can make the worksheet much more flexible.

For example, put your emoji list in D2:D20:

😊
😂
😍
👍
❤️
🎉
🔥
👏

Then you can count each emoji against the text in A2.

For an emoji stored in D2, use:

=(LEN(A2)-LEN(SUBSTITUTE(A2,D2,"")))/LEN(D2)

Copy the formula down alongside your emoji list.

This has a major practical advantage: you don’t have to rewrite your formulas whenever you change the emoji you are analyzing.

If your marketing team later decides to track 🚀 or 💯, simply add it to the list.

How Unicode Affects Emoji Counting

Emoji counting becomes easier once you know a little about Unicode.

Excel provides the UNICODE function, which returns the Unicode code point corresponding to the first character of supplied text. Microsoft documents UNICODE across modern Excel versions, including Microsoft 365, Excel 2024, Excel 2021, and earlier releases.

For example:

=UNICODE("A")

returns:

65

You can also use UNICHAR to convert a Unicode number back into a character.

The important point is that an emoji can involve more than one Unicode element.

For example, a visually simple emoji may include a variation selector. A family emoji may contain multiple people joined with zero-width joiners. A thumbs-up with a skin tone can also contain multiple components.

That means the number of Unicode components is not necessarily the same as the number of emojis a person sees.

Excel’s Newer Compatibility Behavior Matters

This is one of the easiest details to miss.

Microsoft 365 introduced Compatibility Version 2, which changes how functions such as LEN, MID, FIND, SEARCH, and REPLACE handle Unicode surrogate pairs.

In Version 2, many emojis that previously counted as two characters are counted as one. Microsoft gives LEN("😀") as an example: under the newer behavior, it returns 1 rather than 2. Variation selectors used with some emojis can still be counted separately.

You can check the workbook’s compatibility settings through Excel’s calculation settings.

For Microsoft 365, this is especially relevant if you’re comparing results between an older workbook and a newly created workbook.

If two people send you different LEN() results for what appears to be the same text, check the Excel version and workbook compatibility version before assuming that one formula is broken.

How to Use Excel to Count Emojis: Step by Step

Here is the workflow I use when I need to analyze emoji-containing text.

Step 1: Put the text in a cell

Enter or paste the text into A2.

For example:

Amazing result! 🎉🔥👍

Step 2: Check the overall text length

In B2, enter:

=LEN(A2)

This tells you the string length according to your Excel workbook’s Unicode behavior.

Step 3: Decide what you actually want to count

Ask yourself whether you need:

• total characters,
• one particular emoji,
• several known emojis, or
• every emoji in the text.

This decision determines the formula you should use.

Step 4: Count a specific emoji

For example, to count 🔥:

=(LEN(A2)-LEN(SUBSTITUTE(A2,"🔥","")))/LEN("🔥")

Step 5: Copy the formula for other emojis

Change the emoji inside the formula:

=(LEN(A2)-LEN(SUBSTITUTE(A2,"🎉","")))/LEN("🎉")

and:

=(LEN(A2)-LEN(SUBSTITUTE(A2,"👍","")))/LEN("👍")

Step 6: Compare the results

You can now see exactly how many of each tracked emoji occurs in the cell.

This approach is simple, transparent, and easy to check when someone else needs to review your spreadsheet.

Example: Checking Social Media Captions

Suppose you’re preparing a spreadsheet containing Instagram or Facebook captions.

Cell A2 contains:

New collection is here! 🎉 Shop now 🔥 ❤️

You might want to track three things:

Total text length

=LEN(A2)

Number of fire emojis

=(LEN(A2)-LEN(SUBSTITUTE(A2,"🔥","")))/LEN("🔥")

Number of heart emojis

=(LEN(A2)-LEN(SUBSTITUTE(A2,"❤️","")))/LEN("❤️")

This can help when you’re auditing a large collection of captions and want consistent emoji usage.

One thing to watch closely is the heart emoji. ❤️ is not necessarily represented internally as one simple Unicode character because the visible emoji can include a variation selector. That’s one reason visually identical-looking characters can behave differently in text-processing formulas.

Common Mistakes When Counting Emojis in Excel

Mistake 1: Assuming LEN Counts Emojis

LEN measures the length of the string. It doesn’t automatically tell you how many emoji symbols are present.

Use LEN for text length, not as a general emoji detector.

Mistake 2: Assuming Every Emoji Is One Character

That’s not always true at the underlying Unicode level.

Some emojis contain multiple Unicode components, so a visible emoji and its internal representation aren’t necessarily equivalent.

Mistake 3: Forgetting Variation Selectors

Some emojis use variation selectors to control how a character is displayed.

Microsoft specifically notes that variation selectors can still be counted separately in Compatibility Version 2.

Mistake 4: Copying an Emoji From a Different Source

An emoji pasted from Microsoft Word, Google Docs, a web browser, WhatsApp Web, or another application may contain additional Unicode components.

If your formula produces an unexpected result, paste the exact emoji into a separate cell and test that version rather than typing what looks like the same symbol manually.

Mistake 5: Treating Every Pictograph as an Emoji

Symbols such as ★, ✓, ©, and ♥ may look similar to emojis in some fonts, but that doesn’t automatically make them part of the emoji set.

If you’re building an automated report, define which symbols your project considers emojis.

What If You Need the Number of Visible Emojis?

This is the hardest version of the problem.

If you want to answer something like:

“How many emoji symbols would a person see in this cell?”

you are asking about grapheme clusters, not simply Excel’s ordinary character count.

For example, a family emoji can visually appear as one symbol while being represented by multiple Unicode characters connected together.

Excel’s standard text functions are not designed as a complete visual-emoji parser.

For a small dataset, counting a controlled list of emojis is usually practical. For a large dataset containing arbitrary user-generated Unicode text, you may need a more specialized text-processing solution, such as Power Query, VBA, Office Scripts, or an external Unicode-aware processing tool.

Can You Count Emojis Without a Formula?

Yes. For a quick manual check, you can simply use Find.

Press:

Ctrl + F

and paste the emoji you want to find.

Excel can locate occurrences of that exact text, which is useful when you’re checking a small number of cells.

For hundreds or thousands of rows, though, a formula is much more practical because the count can be calculated automatically and reused.

Which Excel Formula Should You Use?

The best formula depends on the job.

The key is not to use a more complicated formula simply because the task involves emojis. If you only need to count 👍, the LEN + SUBSTITUTE method is usually enough.

Frequently Asked Questions

Does Excel count an emoji as one character?

It depends on the Excel version and workbook compatibility settings. Microsoft 365 Compatibility Version 2 counts many Unicode surrogate pairs as one character, while some emoji components, such as variation selectors, can still be counted separately.

How do I count one specific emoji in Excel?

Use LEN and SUBSTITUTE. For example, to count 😊 in A2, use:

=(LEN(A2)-LEN(SUBSTITUTE(A2,"😊","")))/LEN("😊")

The formula removes the selected emoji, measures the difference, and converts that difference into an occurrence count.

Can LEN count all emojis in a cell?

No. LEN counts the text string; it does not classify characters as emojis. It is useful for total text length, but a separate method is required if you want to identify emoji occurrences.

Why does an emoji sometimes seem to count as more than one character?

Some emojis are composed of multiple Unicode elements. Skin-tone modifiers, variation selectors, and zero-width-joiner sequences can make one visible emoji consist of multiple underlying components.

Does Excel have an EMOJI function?

No standard Excel worksheet function specifically counts every emoji in a cell. For known emojis, LEN combined with SUBSTITUTE is a straightforward solution; for unrestricted emoji detection, a more advanced Unicode-aware approach may be necessary.

Counting emojis in Excel is really a Unicode problem disguised as a simple spreadsheet problem. For everyday work, you usually don’t need a complicated system: if you know which emoji you’re looking for, LEN plus SUBSTITUTE gets the job done cleanly.

The important thing is to decide whether you’re counting characters, Unicode components, or visible emoji symbols. Once that distinction is clear, your Excel formulas become much easier to build and, just as importantly, much easier to trust.