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

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.

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.

=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.

Ask Your Question

Weekly Poll

Do you agree with Google's decision to shut down Google+?

Discuss in The Lounge

Poll History