How to Sum Only Numbers and Ignore Text in the Same Cell in Excel (6 Easy Methods)

Dataset Overview

Let’s consider a dataset where we’ve entered the names of 10 employees and their salaries inside the same cell. Our dataset spans the range of cells B5:B14, and we want to calculate the sum of the numbers in cell D5.

If we manually calculate the sum using a calculator, we get a result of 15,830.

Now, let’s explore six easy methods to achieve the same result programmatically:

Method 1 – Using SUM, LEFT and FIND Functions

In our first method, we will use the SUM, LEFT, and FIND functions to get the sum of only numbers and ignore text in the same cell.

• Select cell D5.
• Enter the following formula in the cell:

• Press Enter.

The sum of the numbers will match our manually calculated result. This formula effectively ignores any text within the same cell and only considers the numeric values.

Method 2 – Utilizing SUMPRODUCT, LEFT, and FIND Functions

In this method, we’ll use the SUMPRODUCT, LEFT, and FIND functions to achieve the desired result. Follow these steps:

• Select cell D5.
• Enter the following formula in the cell:

• Press Enter.

The sum of the numbers will match the manually calculated result. This formula effectively ignores any text within the same cell and considers only the numeric values.

Method 3 – Using TRIM and IFERROR Functions

In this approach, we’ll utilize the SUM, TRIM, IFERROR, LEFT, and FIND functions to achieve our goal. Follow these steps:

• Select cell D5.
• Enter the following formula in the cell:

• Press Enter.

The sum of the numbers will match the manually calculated result. This formula effectively ignores any text within the same cell and considers only the numeric values.

Method 4 – Using SUM, IFERROR and RIGHT Functions

In this process, we’ll utilize the SUM, IFERROR, and RIGHT functions to achieve our goal of summing only numbers and ignoring text within the same cell. Note that for this formula to work, the numbers must appear after the text. Let’s break down the steps:

• Select cell D5.
• Enter the following formula in the cell:

• Press Enter.

The sum of the numbers will match the manually calculated result. This formula effectively ignores any text within the same cell and considers only the numeric values.

Method 5 – Summing Only Numbers Based on Categories

In this method, we’ll sum numbers while ignoring text based on specific criteria. To achieve this, we’ll use a combination of functions: SUM, IF, ISNUMBER, VALUE, LEFT, and FIND. Note that we’ll need a different dataset for this approach. Let’s break down the steps:

• Select cell F5.
• Enter the following formula in the cell:

• Press Enter.

• Double-click on the Fill Handle icon to copy the formula up to cell F7.

You’ll see the sum of all the numbers based on our three desired categories.

Method 6 – Embedding VBA Code to Sum Only Numbers and Ignore Text in Excel

If you want to sum only the numeric values in a range of cells while ignoring any text, you can achieve this using VBA (Visual Basic for Applications) code. Follow these steps:

• Go to the Developer tab in Excel.
• Click on Visual Basic to open the Visual Basic Editor.
• If you don’t see the Developer tab, you’ll need to enable the Developer tab.
• Alternatively, you can press Alt + F11 to directly open the editor.

• In the Visual Basic Editor, click on the Insert menu.
• Choose Module to insert a new module.

• In the empty code editor, enter the following VBA function:

Function sum_only_numbers(main_range As Range, Optional str_delim As String = " ") As Double
    Dim data As Variant, long_num As Long
    For Each elem In main_range
      data = Split(elem, str_delim)
      For long_num = LBound(data) To UBound(data) Step 1
        sum_only_numbers = sum_only_numbers + Val(data(long_num))
      Next long_num
    Next elem
End Function

• Press Ctrl + S to save the code.
• Close the Visual Basic Editor.
• Select the cell where you want to display the sum (e.g., cell D5).
• Enter the following formula:

• Press Enter.

You’ll now see the sum of all the numeric values in the specified range (B5:B14), excluding any text. This approach successfully allows you to sum only numbers and ignore text within the same cell in Excel.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Sum If Cell Contains Text in Another Cell in Excel
• How to Sum Names in Excel
• How to Assign Value to Text and Sum in Excel
• How to Sum Text Values Like Numbers in Excel

<< Go Back to Excel Sum If Cell Contains Text | Excel SUMIF Function | Excel Functions | Learn Excel