COUNTIF text field for pasted data

[seh0872]

So...I'm trying to use COUNTIF to count all cells with text data in a column formatted as text. I've used the formula COUNTIF($A:$A,"*") to accomplish this. Works fine if I type the data in. But much of the data is pasted from another source (using paste values). The COUNTIF formula will NOT consider any pasted data.

For the simple formula, I could convert from COUNTIF to COUNTA, but this won't work for my two variable formulas (COUNTIFS).

What gives? Any ideas? I need the cells to be text format to ensure that the leading zeros are not just showing, but are included in another cell that concatenates that data.

 

[6StringJazzer]

The COUNTIF function does not care whether data was pasted or not. Can you show an example of what data you have, and what the incorrect result of COUNTIF is?

This column is formatted as Text. I put three values in it. I copied those values to other cells, then copied them back to column A. Result is correct:

$scratch.xlsm
	A	B	C
1	000123		6
2	000456
3	000789
4	000123
5	000456
6	000789
COUNTIF demo

Cell Formulas
Range	Formula
C1	C1	=COUNTIF(A:A,"*")

 

[seh0872]

In exploring the dataset to answer you, I discovered the issue. The original data from which I am sourcing the data has data in both text format and number format. The format in the ribbon says "text" for the entire column, but certain cells are clearly not text. As soon as I add a leading zero, it "converts". I wonder if this is the opposite of the numbers stored as text problem (in this case I have text stored as numbers).

Using the "Text to Columns" trick converted all data to text, then pasted it and -- voila! -- all is OK.

Just another problem of Excel being secretive and frankly lying to you about how it is reading the data.

 

[6StringJazzer]

The format in the ribbon says "text" for the entire column

If you select more than one cell, or an entire column, and the formatting is not the same in all cells of the selection, the format box will show the format for the upper-left cell of the selection.

 

[bebo021999]

Count "text" + count "value":

=COUNTIF(A:A,"*")+COUNT(A:A)