How to Sum If a Cell Contains Numbers in Excel

A single column can contain numbers, text, and text with numbers in an Excel worksheet. Such as 500$, 450 dollar, 750$ (paid yesterday), or even words like unpaid. Excel understands them as text, and the regular SUM function won’t work. Using Power Query, VBA, or even quick manual tricks to extract and sum only the numeric values, we can sum numbers.

When your dataset contains numbers, text, or text with numbers, it means the data is mixed. By writing a small custom function in VBA, we can automatically extract the numeric part of each cell, while ignoring plain text without any numbers like “unpaid” or “paid.” After that, we can simply call the function in our worksheet and sum the results easily.

Steps:

➤ Press  Alt  +  F11  to open the VBA editor.
➤ Click Insert > Module.

➤ Paste the following code in the module.

Function ExtractNumber(cell As Range) As Double
    Dim re As Object, matches As Object
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "\d+"
    re.Global = True
    If re.test(cell.Value) Then
        Set matches = re.Execute(cell.Value)
        ExtractNumber = CDbl(matches(0))
    Else
        ExtractNumber = 0
    End If
End Function

➤ Close the VBA editor
➤ Select cell F2 and write this formula to extract numbers.

➤ Drag the numbers. Only plain text will show 0

➤ Write the formula in cell F13 to see the total result.

Power Query is very useful for large datasets. With a few clicks, we can extract only the numbers from a text cell, replace errors with zero. We can refresh after the query is created and can apply it to updated data without redoing all the steps.

Steps:

➤ Select the dataset, click Data> From Table/ Range

➤ Check “My table has headers” and click OK

➤ The table is created, and click on the Sales Record column

➤ Click Add Column> Custom Column option

➤ Name the new column “ ExtractedNumbers” and write this custom column formula :

Then click OK.

➤ Then click Transform and the whole number option, close and load the query.

➤ Select cell B13 and write this formula :

➤ Press Enter and see the sum of the numbers is 4070.

By combining LET, SEQUENCE, and MID functions, we can check each character of a text string, extract the numbers from mixed data. After that, we can sum those numbers.

Steps:

➤ Select the cell F2 and write this formula:

➤ Press Enter, and you will see that the first number is extracted. Drag down to see other extracted numbers.

➤ Select the cell F13 and write this formula :

➤ Press Enter and see the result is 4070

Flash Fill is the fastest method. First, type the number as you want, and then Excel will recognise the pattern and automatically fill in the rest. Here, we are using the flash fill method to extract numbers from the mixed data.

Steps:

➤ Write the first numeric value “500” in cell F2.

➤ Click  Ctrl  +  E  to open Flash Fill to fill down the other numbers. Flash Fill will show the suggestions, and the suggestions will be accepted automatically. It will fill the numbers, and plain text will remain unchanged.

➤ Select the cell F13 and write this formula :

➤ Press Enter and see the result is 4070

Using the Regex functions such as REGEXEXTRACT, we can directly extract the numeric values from any cell.

Steps:

➤ Click on the cell F2 and write this formula:

And you will see the first extracted value is 500

➤ Drag down below to see the other extracted numbers.

➤ Select the cell F13 and write this formula.

➤ Press Enter and see the result is 4070

Frequently Asked Questions

Will blank cells cause any problem in summing cells that contain mixed data?

No. Blank cells are treated as 0 by Excel’s SUM-related formulas, so they won’t affect the result.

What will happen if my dataset contains error values like #N/A or #VALUE!?

Error values can break your formula. To avoid this, wrap your range with IFERROR or use:

=SUM(IFERROR(VALUE(TOCOL(B2:E11,1)),0))

All the errors will be treated as 0.

Is there a way to sum numbers in mixed data using VBA?

Yes. A simple VBA macro with IsNumeric will add only numeric values. This is useful if your dataset is extremely large and formulas slow down performance.

Why does SUM not work directly on cells?

When numbers are mixed with symbols or words (like $, dollar, or paid), Excel treats the entire cell as text. The SUM function only works on true numeric values, so you must first extract the numbers.

Wrapping Up

In this article, we discussed five different methods to sum numbers if a cell contains mixed data. That means the numbers, plain text, and text with numbers are present in columns in Excel. We have shown step by step how you can sum those numbers. Feel free to download the practice file and share your thoughts and suggestions in the comment box. Hopefully, you will enjoy this article.