Many professionals struggle when Excel treats numbers containing text units as text, blocking direct summation. When consolidating project budgets from standard funding sources, manual extraction becomes tedious. Fortunately, leveraging advanced array formulas grants teams immediate, automated calculation capabilities without losing vital unit context.

Stipulation: This approach requires consistent unit formatting to prevent formula syntax errors. For example, applying this technique to sum "USD" or "lbs" from standard inventory lists ensures clean, automated reporting. Below, we outline the exact formula configurations to achieve this.

Excel Formula to Add Raw Numbers with Text Units

Excel is an incredibly powerful tool for data analysis, but it has one fundamental limitation: it cannot perform mathematical operations on text. If your spreadsheet contains cells like "10 kg", "150 USD", or "5 hrs", Excel treats these cells as text strings rather than numbers. Consequently, if you try to sum them using a standard =SUM(A1:A10) formula, Excel will return 0.

This is a common issue when importing data from external databases, web scraping, or collaborating with colleagues who manually type units into cells. Fortunately, you don't have to manually delete the text units from every cell. In this comprehensive guide, we will explore the best ways to sum raw numbers with text units, ranging from the "clean" proactive approach of custom formatting to advanced formulas and VBA macros for cleaning dirty data.

Method 1: The Proactive Solution – Custom Number Formatting (Best Practice)

The absolute best way to handle numbers with units in Excel is to keep the data purely numeric while displaying the units visually. By separating the raw numerical data from its visual presentation, you preserve Excel's ability to perform native calculations like SUM, AVERAGE, and MULTIPLY without any complex formulas.

To apply custom formatting so that numbers display with text units automatically:

1. Select the cells containing your numbers (or the cells where you plan to enter them).
2. Press Ctrl + 1 (Windows) or Cmd + 1 (Mac) to open the Format Cells dialog box.
3. In the Category list on the left, click on Custom.
4. In the Type text box, enter one of the following codes based on your unit:
   • To display "kg" after a whole number: #,##0" kg"
   • To display "USD" after a currency amount: $#,##0" USD"
   • To display decimals with units: #,##0.00" lbs"
5. Click OK.

Now, if you type 10 into the cell, Excel will display 10 kg, but the actual value stored in the cell is just the raw number 10. You can now use =SUM(A1:A10), and Excel will calculate the sum instantly and format the total with the unit automatically.

Method 2: Summing Cleaned Data with SUBSTITUTE and SUMPRODUCT

If you have inherited a spreadsheet where text units are already typed directly into the cells (e.g., "25 kg", "12 kg", "40 kg"), you cannot use custom formatting immediately without cleaning the data first. If all the units are identical, you can use a combination of SUBSTITUTE, VALUE, and SUMPRODUCT.

The Formula

=SUMPRODUCT(VALUE(SUBSTITUTE(A1:A10, " kg", "")))

How It Works

• SUBSTITUTE(A1:A10, " kg", ""): This function scans the range A1:A10, finds the text string " kg", and replaces it with an empty string (""). This strips the unit away, leaving only the text representation of the number (e.g., "25").
• VALUE(...): Since the SUBSTITUTE function returns text, VALUE converts those text numbers back into actual numeric values that Excel can calculate.
• SUMPRODUCT(...): Normally, SUBSTITUTE only works on a single cell. SUMPRODUCT forces Excel to process the entire array of cells (A1 through A10) without requiring you to enter the formula as a complex legacy array formula (using Ctrl+Shift+Enter). It then sums up the converted numeric array.

Note: If your data has inconsistent spacing (e.g., some cells have "10kg" and others have "10 kg"), you can nest another SUBSTITUTE function to strip spaces first:

=SUMPRODUCT(VALUE(SUBSTITUTE(SUBSTITUTE(A1:A10, " ", ""), "kg", "")))

Method 3: Summing Dynamic Units in Excel 365 (Using TEXTBEFORE)

If you are using the modern, subscription-based version of Excel (Microsoft 365 or Excel for the Web), you have access to powerful new text manipulation functions. The TEXTBEFORE function makes extracting numbers from trailing text units incredibly simple and elegant.

The Formula

=SUM(VALUE(TEXTBEFORE(A1:A10, " ")))

How It Works

• TEXTBEFORE(A1:A10, " "): This function extracts all text appearing before the space character. For instance, if a cell contains "45.5 lbs", it extracts "45.5".
• VALUE(...): Converts the extracted text string of numbers into real, calculate-ready numeric values.
• SUM(...): Because Microsoft 365 natively supports dynamic arrays, SUM automatically calculates the total of the spilled array of numbers without needing SUMPRODUCT.

What if there is no space between the number and the unit? (e.g., "100m", "50m")
If there is no space delimiter, you can use a formula that dynamically finds where the text characters start. However, if the units are of a fixed length (e.g., always 1 character like "m", or 2 characters like "kg"), you can use the LEFT and LEN functions:

=SUMPRODUCT(VALUE(LEFT(A1:A10, LEN(A1:A10) - 2)))

This formula calculates the length of each cell, subtracts 2 (for the "kg"), and extracts that number of characters from the left side of the string before summing them up.

Method 4: Summing Numbers with Varied, Mixed Text Units

In real-world data, you may encounter columns with a complete mix of units (e.g., some rows have "lbs", others "kg", some "g", and some have no units at all). If you just want to extract and sum the raw numbers regardless of what the units are, we can construct an advanced array formula using LET and MAP in Excel 365.

The Modern Excel 365 Formula

=SUM(MAP(A1:A10, LAMBDA(cell, VALUE(TEXTJOIN("", TRUE, IFERROR(MID(cell, SEQUENCE(LEN(cell)), 1)*1, ""))))))

How This Beast Works

1. SEQUENCE(LEN(cell)): Creates an array of numbers representing the position of every single character in the cell.
2. MID(cell, ..., 1): Breaks the cell down into an array of individual single characters.
3. *1: Attempts to multiply each character by 1. Numeric characters will remain numbers, while text units (like "k", "g", "l", "b", "s") will trigger a #VALUE! error.
4. IFERROR(..., ""): Replaces all error-generating text characters with empty text values.
5. TEXTJOIN("", TRUE, ...): Concatenates the remaining numbers back together into a single string, ignoring the empty text values.
6. VALUE(...): Converts the joined numeric string back into an actual number.
7. MAP(..., LAMBDA(...)): Repeats this entire extraction sequence for every individual cell in the specified range (A1:A10).
8. SUM(...): Totals all the cleaned numbers.

Method 5: The Ultimate VBA Custom Function (User-Defined Function)

If you regularly deal with dirty data containing inconsistent mixed text and numbers, writing complex array formulas can be exhausting. Instead, you can create a Custom User-Defined Function (UDF) in VBA called SumNumbersOnly. This gives you a reusable formula that handles any kind of text units seamlessly.

The VBA Code

To add this function to your workbook, press Alt + F11 to open the VBA Editor, click Insert > Module, and paste the following code:

Function SumNumbersOnly(rng As Range) As Double
    Dim cell As Range
    Dim i As Integer
    Dim numStr As String
    Dim total As Double
    
    total = 0
    For Each cell In rng
        numStr = ""
        If cell.Value <> "" Then
            For i = 1 To Len(cell.Value)
                Dim char As String
                char = Mid(cell.Value, i, 1)
                ' Keep numbers, decimal points, and negative signs
                If IsNumeric(char) Or char = "." Or char = "-" Then
                    numStr = numStr & char
                End If
            Next i
            If IsNumeric(numStr) Then
                total = total + CDbl(numStr)
            End If
        End If
    Next cell
    SumNumbersOnly = total
End Function

How to Use It

Once you save the code and close the VBA Editor, you can use this brand-new custom function directly inside your Excel worksheet just like any standard formula:

=SumNumbersOnly(A1:A10)

This macro-powered formula will look through the cells, isolate every numeric value (including decimals and negative values), ignore all text units, and sum them instantly.

Remember to save your Excel workbook as an Excel Macro-Enabled Workbook (.xlsm) to ensure your custom VBA function is saved.

Summary of Solutions: When to Use Which?

To help you decide which tool is best for your specific workbook, refer to this quick comparison table:

By understanding how Excel treats data types, you can bypass the frustration of zero-sum errors. Whenever possible, default to Custom Number Formatting to keep your workflows efficient and standard. When dealing with imported data, select the cleaning formula that best matches the structure of your units to keep your formulas robust and processing times fast.