Press "Enter" to skip to content

How To Sum Cells With Text And Numbers In It

Kaustubh Patel 0

One of my friends wants to sum total the number posted along with the text in cells. Actually, these numbers represented the total numbers of entries posted by a branch in a particular month.

For example, If Ahmedabad branch has posted 4 entries then he writes ‘Entries_Posted_4’ against the Ahmedabad branch. Similarly, he mentions the total number of entries posted against all the branches.

Next, in the end, he does the sum total of these cells containing numbers and text and calculates the total number of entries posted during the month by all the branches.

Something like this.

Actually, he needs to prepare this report for control purposes. At month-end. he needs to ensure no entries are left behind for the posting.

Now, since it is not possible to sum cells containing numbers and text as well. Currently, he is doing this activity manually.

If this problem sounds familiar to you then you’re at the right place. Here, in this article, I will tell you how to sum cells containing both numbers and text as well.

You can also use the same formula to split cell values into text and numbers.

So, What are we waiting for? Let’s find out how to sum cells with text and numbers?

But before starting with the article, let me clarify that this can be also done using the ‘Text to Column’ option available in the excel.

But that process will be time-consuming and will require creating an additional column to get the answer.

Text to Column will not work if you want to get the answer directly in the cell within one click.

Hence, I have created a formula to sum the cells containing numbers and text as well. Using this excel formula, you can easily get the answer ’63’ in Grand Total.

Sum Cells With Text And Number In It

First of all, let’s divide this question into two categories.

  1. If maximum number of entries posted is less than 10.
  2. If the number of entries posted is greater than 9.

Note: The reason behind dividing the question is to make it easier. I have a short and simple formula but it only works with the cases having less than 10 entries posted.

So, let’s start the article below.

#1. Maximum Entries Posted is 9

In such a case, the formula will be

{=SUM(--RIGHT(B4:E4,1)+0)}

How Does This Formula Work?

This formula will first identify the first letter starting from left.

Assuming, we have used this formula in cell F4, the formula will look like this

{=SUM(--RIGHT("4","0","1","0")+0)}

Next, I will add ‘0’ to the numbers identify through the RIGHT() function. This is because numbers identified by the RIGHT() function are in text format. And, SUM() function will not work on numbers which are in text format.

Hence, we add zero to it and then convert them into numbers. Now the formula will look like this.

{=SUM("4","0","1","0")}

Next, the SUM() function will do the sum total of these numbers and return ‘5’ as an answer in Cell F4.

Note: Since this is an array formula, it will not work with normal ‘Entre’. Instead, you need to press Ctrl+Shift+Entre. Ctrl Shift-Enter is one of the shortcuts used in Excel to perform the calculations with array formulae. Please remember, this is represented by curly bracket ‘{‘ and ‘}’ before and after the formula.

#2. Maximum Entries Posted is More Than 9

In such a case, the above formula will not work because it only extracts one letter from the given string.

But, don’t worry, I have created a new formula to sum such numbers which are greater than 9. The formula is as follows.

{=SUM(--RIGHT(B7:E7,LEN(B7:E7)-SEARCH("X",SUBSTITUTE(B7:E7,"_","x",2))))}

This formula was entred in the F7 cell and the answer is ’15’.

Please do not get scared after looking at the above formula.

Let’s understand first how this formula work.

How Does This Formula Work?

Let us understand this formula taking only one cell as an example. Here, I am considering cell B7 for this formula.

SEARCH("X",SUBSTITUTE(B7:E7,"_","x",2))

The SUBSTITUTE() function will substitute the character “_” with “X” in second instances.

I am substituting this in the second instance because the “_” appears twice in the text.

After Substituting, the text will look like this in cell B7 (Entries_PostedX15).

Now, the SEARCH() function will find ‘X’ in the cell and return the number at which it is appearing.

In cell B7, the character “X” is appearing in the 15th position.

Now, I will deduct this 15 from the total length of the text string that is 17. The answer will be 2 (17-15).

Next, the RIGHT() function will extract the 2 characters from the string starting from the right side.

And, at last, the SUM() function will do the sum total of these numbers.

Note: Since this is an array formula, this calculation will be done for all the cells ranging between B7: E7. Of course, as discussed earlier, I will press Ctrl+Shift+Entre to get the answer.

For better understanding, I have colored the formula used in cell F7 and attached below for your reference.

Colored Formula to sum total numbers in a cell for better understanding

Please refer to the above formula in the given order (Red, Green, Purple, Orange, and Blue) for better understanding.

Over to You

The above formulas will be a great help for users who prepare reports at a unit level like Kg, Meter, Packets, etc.

Suppose, you have a file where you have used units besides mentioning the numbers against your order. For example, the Order of 7 Packet from customer X.

Now, in case if you want to know the total number of packets than the only way to do is extracting numbers from text and then do the sum of it.

This can be done using Text to Column or formula mentioned above in this article.

At last, feel free to contact us if you face any problem in understanding or implementing the above formula or any part of this article. You can contact us through the ‘Contact Us’ page or leave your message in below comment box.

Leave a Reply

Your email address will not be published. Required fields are marked *