• Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us
Excel

How to count the number of occurrences of a character in Excel?

0 Comments

There are times when you need to count the number of occurrences of a value or character in a spreadsheet. For example, counting the number of workdays, the number of shifts worked, or the number of products sold in a day, a month, etc. If you have a large amount of data, you can’t count manually. In this case, use the COUNT function in Excel to save time and effort. This article from Buffcom.net will guide you on how to use the functions in Excel to count the number of occurrences of a character.

For example, we have the following data table. The requirement is to count the number of times the character “n” appears in those names.

How to count the number of occurrences of a character in Excel?

How to count the number of occurrences of a character in Excel?

To perform this task, we need to combine 2 functions: LEN and SUBSTITUTE. First, let’s explore the structure of these 2 functions.

1. LEN function

The LEN function in Excel is used to measure the length of a character string and returns the exact number of characters in the character string, including spaces.
Function syntax: =LEN(text)

Where:
Text: required argument, is the text that you want to count the number of characters.

Note: Spaces are also counted as characters by the function.

Applying to the above example, we just need to enter the formula =LEN(A2) to quickly count the number of characters, including spaces, in cell A2 as 16.

How to count the number of occurrences of a character in Excel?

How to count the number of occurrences of a character in Excel?

2. SUBSTITUTE function

The SUBSTITUTE function is used in Excel to replace the old text string with a new text string.
Function syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])

Where:

  • Text: required argument, is the text or cell reference that you want to replace the characters in.
  • Old_text: required argument, is the text you want to replace.
  • New_text: required argument, is the text you want to replace old_text with.
  • Instance_num: optional argument, specifies the number of times you want to replace old_text with new_text.

 

Applying to the above example, we just need to enter the formula =SUBSTITUTE(A2;B2;””) to quickly remove the character “n” from the name in cell A2.

How to count the number of occurrences of a character in Excel?

How to count the number of occurrences of a character in Excel?

3. Count the number of occurrences of a character in a cell

To count the number of times the character “n” appears in a cell, enter the formula as follows:

=LEN(A2)-LEN(SUBSTITUTE(A2;B2;""))

This formula counts the number of characters in cell A2, then subtracts the number of characters in cell A2 after removing the character “n”. This will return the number of times the character “n” appears.

How to count the number of occurrences of a character in Excel?

How to count the number of occurrences of a character in Excel?

4. Count the number of occurrences of a character in a data array
If you want to count the number of times the character “n” appears in an entire column of names at once, enter the formula as follows:

=SUMPRODUCT(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7;"n";"")))

As you can see, the result will return 12, which is the same as the total result returned above.

How to count the number of occurrences of a character in Excel?

How to count the number of occurrences of a character in Excel?

So, the article above has guided you on how to count the number of times a character appears in Excel. Hopefully, the article will be useful for you in your work. Wish you success!

6
57 Views
4 essential Excel spreadsheet editing operations you should know by heartPrev4 essential Excel spreadsheet editing operations you should know by heartMarch 8, 2023
Calculate the number of days between two dates in ExcelMarch 8, 2023Calculate the number of days between two dates in ExcelNext

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts
  • Retrieve Data Based on Data Validation
    September 7, 2023
    Excel
  • How to Convert Multiple Rows to Columns and Rows in Excel?
    September 6, 2023
    Excel
  • VLOOKUP for Automatic Discount in Data Validation
    September 6, 2023
    Excel
  • How to Convert Text Dates to Dates in Excel?
    September 5, 2023
    Excel
Categories
  • Blog 80
  • Excel 754
  • Powerpoint 36
  • Software tricks/tips 128
  • Uncategorized 8
  • Word 109
Tags
the ISERROR
Top rated products
  • Avast Ultimate Suite 2021 1 Year 10 Devices Global
    Rated 5.00 out of 5
    $54.00
  • Windows 11 Pro Key Global
    Rated 5.00 out of 5
    $11.00
  • Avast Premium Security 2021 10 Devices 2 Years Global Avast Premium Security 2021 10 Devices 2 Years Global
    Rated 5.00 out of 5
    $41.00
  • Kaspersky Small Office Security 15 PCs + 15 Mobiles + 2 Servers 1 Year Kaspersky Small Office Security 15 PCs + 15 Mobiles + 2 Servers 1 Year
    Rated 5.00 out of 5
    $201.00
  • Buy Windows 11 Professional MS Products CD Key
    Rated 5.00 out of 5
    $11.00
Products
  • Buy Office 2021 Professional Plus Key Global For 5 PC
    Rated 5.00 out of 5
    $45.00
  • Buy Office 2021 Professional Plus Key Global Bind To Your Microsoft Account
    Rated 4.83 out of 5
    $49.00
  • Windows Server 2022 Datacenter Key Global
    Rated 5.00 out of 5
    $15.00
  • Windows Server 2022 Standard Key Global
    Rated 4.47 out of 5
    $15.00
  • Windows Server 2022 Remote Desktop Services Device Connections (50) Cal Key Global
    Rated 5.00 out of 5
    $45.00

Buffcom.net always brings the best digital products and services to you. Specializing in Office Software and online marketing services

BIG SALE 50% IN MAY

Microsoft Office
Microsoft Windows
Anti-Virus
Contact Us

Visit Us:

125 Division St, New York, NY 10002, USA

Mail Us:

buffcom.net@gmail.com

TERMS & CONDITIONS | PAYMENT GUIDE  | SHIPPING POLICY  | REFUND POLICY

Copyright © 2019 buffcom.net  All Rights Reserved.