Solved Summing numbers from text strings in Excel

Microsoft Excel 2010 - complete product...
October 26, 2011 at 16:20:02
Specs: Windows 7
Hi, I hope someone can help me. Maybe the problem is easy, but let's see your opinion. I have a range of cells that have numeric and textual data (in the same cell) and I'd like to sum only the numeric data. Can someone help me with creating/using some formula to calculate it?
I have something like this:
A1 1 (apple)
A2 1 (apple)
A3 2 (apple)
A4 1 (orange)
A5 3 (plum).
I'd like to get the sum of all e.g. fruits. Ideally, I could get the sums per individual fruit.
Manual counting still is an option, but I'm 20 years short to finishing it :).
Any idea?
Thanks

See More: Summing numbers from text strings in Excel

Report •

#1
October 26, 2011 at 21:34:26
✔ Best Answer
The brute force method is this:

=SUM(LEFT(A1,1),LEFT(A2,1),LEFT(A3,1), etc.)

If you need to keep the data in the cells intact, you could put this in B1 and then drag it down:

=LEFT(A1,1)

That will give you a list of numbers that you could Sum.

If it's OK to split the cells, select Column A and perform Data...Text-to-Columns...Delimited by a space.

That will create a column of numbers and a column of text.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 27, 2011 at 13:10:31
Thanks DerbyDad03!
The method with the LEFT doesn't work, SUM is not recognizing the values as numbers and is not summing them. The "text to columns" option helped a lot, although not the way you suggested. I used fixed "fixed width" because some of my cells contain more than one word (e.g. red apples); so I used one column for the numbers and another one for the text.
Thanks a lot for the help! I appreciate it.

Report •

#3
October 27, 2011 at 15:12:55
=LEFT(A1,1) * 1 or =LEFT(a1,1,) + 0 or anything similar will return a number which can then be summed.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question