I often find myself helping people crunch numbers in Excel that are generated by some other database system and exported as CSV. Â These are generally log files of some sort. Â The data gets exported as CSV and pulled into Excel. Â Then you go about inserting formulas to get the various sums and counts that are necessary for whatever ad-hoc reporting objective is at hand.
CSV files are text files, but they their contents are often intended to represent numeric values. Â Simply setting the data format of a column isn’t enought to transform existing values from numbers-as-text to text-itself (forgive me if I’m starting to sound like Martin Heidegger, I don’t know a better way to put it). Â Changing the columns data type won’t help you for existing cells. Â It will only help with new cells or cells that you, after changing the format, happen to edit.
These numbers-as-text are problematic because certain key Excel count functions (COUNT(), COUNTIF()) silently ignore non-numeric values. Â COUNTA() can be used to count all non-blank cells. Â But, if you’re trying to apply COUNTIF() to these numbers-as-text, you’ll just keep getting zero–which is, actually, the correct answer.
What you need is the VALUE() function. Â It takes things that look like numbers (but, as far as Excel is concerned, are not numbers) and turns them into numbers. Â So, if you have a column of numbers that Excel insists on treating as text (say, column G, for example) you just create new column (let’s call it H), insert “=VALUE(G:G)”, and fill down. Â Then you can apply whatever COUNT() and COUNTIF() functions you like to column H.
There are three quick ways to tell if you’re looking at numbers-as-text instead of the real thing. Â By defult, Excel justifies numbers to the right and text to the left. Â So, if the data is freshly imported, that’s the most obvious attribute to check. Â But that setting can easily be overridden. Â So, if some other hands have been on the data, you can try counting the column (e.g. COUNT(G:G)), which should give you zero. Â Or, if you want to test each cell individually, you can create a new column, insert the T() function and fill down. Â T() returns nothing if the value is numeric. Â If it’s text, if will repeat the string it finds. Â So, if your original column and the one generated by T() are identical, you know you’re looking at text.