LEN and SUBSTITUTE formula in Excel with Examples

When working with Excel, we often need to manipulate text strings, such as calculating the length of a text with and without spaces. This is where the LEN and SUBSTITUTE formula in Excel come in handy. In this post, we’ll walk you through how to use these formulas step by step with examples to help you gain more control over your data.

Table of Contents

What Are the LEN and SUBSTITUTE Functions?

Before diving into the example, let’s first understand what these functions do:

LEN Function: The LEN function in Excel is used to count the number of characters in a text string, including spaces.

Syntax:

=LEN (text)

SUBSTITUTE Function: SUBSTITUTE replaces one text string with another within a specific text. In our case, we will use it to remove spaces from a string.

Syntax:

=SUBSTITUTE(text, old text, new text, [instance Num])

When we combine the LEN and SUBSTITUTE functions, we can easily calculate the length of a text string both with and without spaces. Exciting, right? Let’s move on to the practical example to see this in action.

Example: Using LEN and SUBSTITUTE Formula in Excel

We’ve got a simple dataset that contains names along with their length, both with and without spaces. Let’s walk through how to apply the LEN and SUBSTITUTE formulas to achieve this.

Our Dataset

This data can be found in columns A3 to C23. Column A contains the names, Column B has the length of the name with spaces, and Column C contains the length of the name without spaces.

Step-by-Step: Applying the LEN and SUBSTITUTE Formulas

Let’s break this down:

Find the Length with Space: To find the length of each name, including spaces, we use the LEN formula. Here’s how it works for Finley Burton:

=LEN(A4)

The result would be 13, as “Finley Burton” has 13 characters, including the space between the first and last name.

Find the Length without Space: Now, to calculate the length of the name excluding spaces, we use a combination of LEN and SUBSTITUTE formulas:

=LEN (SUBSTITUTE(A4,” “,””))

The SUBSTITUTE formula removes the spaces, and then LEN counts the remaining characters. For “Finley Burton,” the result is 12.

Apply the Formula to the Whole Dataset: Simply drag the formula down for all the names in your dataset to get the length with and without spaces for each name. Easy, isn’t it?

Final Output:

After applying these formulas, here’s the output:

Why Use LEN and SUBSTITUTE Together?

You might be wondering why we even need to combine these two functions. Well, it comes down to data cleaning. Whether you’re analyzing customer data, managing inventory, or working with any text-heavy data, knowing how to manipulate text strings like this can make your work faster and more efficient.

Key Benefits:

Quick Calculation: With just a few formulas, you can instantly find the length of any string.
Improved Data Accuracy: By eliminating spaces, you ensure that you’re working with clean and accurate data.
Efficient Workflow: These functions save you from manually counting characters or removing spaces, streamlining your data processing.

Try It Yourself!

Give it a go and see how powerful these formulas can be when working with text data. Whether it’s counting characters or cleaning up your data, the LEN and SUBSTITUTE functions in Excel are essential tools to have in your arsenal.

If you found this guide helpful, be sure to check out our YouTube video for more examples and tips!

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert