How to calculate the sum of the cells which includes both numbers and word

Copper Contributor

In the attached screenshot "I want the total to show as 20 but it is only calculating numbers"


Excel SS.jpg

4 Replies

@Rehanparkar I would not recommend capturing data like that, but if you have Excel365 or 2021, this would work:



Thank you for your reply
do you have any other suggestion for me to record where i can show word and number both in the same cell and the sum will show up the actual total?

@Rehanparkar I would just separate the texts from the numbers. That is, each in their own cells. Something like this:



Thank you for the suggestion, but my requirement is to show the text and number in the same cell
thank you for your formula, it worked..appreciate it