# 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

#1
October 26, 2011 at 21:34:26
 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