I have an Excel table with some cells that are numbers and some cells that are strings. I want a function that can add search through the table, and if the cell is a number, add it(+) and if it's a string, ignore it.

I know how to do this easily in say C# but how do I do this in Excel?

This is what I want the equivelant of:

object[] celldata = new object[] {"blabla",2,3,"blabla",3,"blabla"}

int additioncell;

foreach(object cells in celldata)
    additionalcell += (int) cells;

How do I convert this into an Excel function?


The sum() function should work. It will ignore the strings.

Additionally, you can use isnumber() and istext() to check if a cell is a number or text.


Right click on the cells in question and create a custom format.enter image description here

  • Keep in mind that formatting this way, in general, may affect cell values in VBA. If I were to make a custom type like "derp"@, then my Range(Address).Value would look at the unformatted value. To get the formatted value, you would use Range(Address).Text.
    – Zairja
    Aug 2 '12 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.