In this tutorial, you will learn how to sum cells with text and numbers in Excel.
Take the following screenshot as an example: I have a list of values that contains both numerical and textual strings, and I only want to total the numbers based on the same text. In general, you can’t sum the items in a list directly with a text string. Here, I’ll show you different formulas to handle this work.
Once ready, we’ll get started by utilizing real-world examples to show you how to sum cells with text and numbers in Excel.
Table of Contents
Sum Cells with Text and Numbers in Excel
Before we begin we will need a group of data to sum cells with text and numbers in Excel.
Step 1
First, you need to have a clean and tidy group of data to work with.
Step 2
In this example, we want to sum the total weight for all items. To do so, we can insert the following formula =SUM(SUBSTITUTE(B2:B8, “KG”, “”)+0).
Step 3
Once we are done, you will be able to sum cells with text and numbers in Excel.