I put TRIM in the formula to remove the space but it still doesn't locate it. What am I not seeing?

[Eloise T]

The Work Order number in cell A3 has a space after it. The duplicate locating formula can't find the other duplicate in row A15 because of that space. I put TRIM in the formula to remove the space(s) but the formula still doesn't seem to TRIM off the space. Please see attached file. What am I not seeing?

 

[Nebu]

Hi:

I have fixed it, Countif wont take trim function, you will have to convert count if into an array formula for the trim to work and since you are counting text it should be CountA.

Thanks

 

[Chihiro]

That's because the range you are looking at $A$3:$A$100.
The actual cell A3 still has trailing space, only 1 match will be found for TRIM($A3), & formula evaluates to " ".

Edit: Nebu beat me to it

 

[Eloise T]

Hi:

I have fixed it, Countif wont take trim function, you will have to convert count if into an array formula for the trim to work and since you are counting text it should be CountA.

Thanks

THANK YOU!

 

[Eloise T]

Hi:

I have fixed it, Countif wont take trim function, you will have to convert count if into an array formula for the trim to work and since you are counting text it should be CountA.

Thanks

That's because the range you are looking at $A$3:$A$100.
The actual cell A3 still has trailing space, only 1 match will be found for TRIM($A3), & formula evaluates to " ".

Edit: Nebu beat me to it

Please reread my description. I said I purposely put a space in the work order number to test and make sure the formula covers every base. This is due to receiving work order numbers from uncaring individuals that are in a hurry to send me their information without checking their work. They can be in error but I cannot, so I have to make sure my formulas account for their ineptness.

I'm not certain I understood your reply. Are you saying COUNTA works better with text than COUNTIF...or ?

 

[Chihiro]

Hi,

To clarify, my statement has nothing to do with whether the space is there on purpose or not

But how TRIM is applied in your formula.

In such case as your's. I'd recommend adding helper column and applying trim there. Adds extra step, but formula will be much easier to manage/understand for anyone that may need to cover for you during vacation etc.

 

[Chihiro]

Since COUNTIF(TRIM(RANGE),TRIM(CELL) does not work.

Nebu suggested going the route of converting COUNTIF to array formula to apply TRIM to a range.

So you need COUNT(IF()). However, since COUNT only counts numbers, you need to use COUNTA to count text.

Therefore, below portion in Nebu's formula.
"COUNTA(IF(TRIM($A$3:$A$18)=TRIM($A3),$A$3:$A$18))"

 

[Eloise T]

Since COUNTIF(TRIM(RANGE),TRIM(CELL) does not work.

Nebu suggested going the route of converting COUNTIF to array formula to apply TRIM to a range.

So you need COUNT(IF()). However, since COUNT only counts numbers, you need to use COUNTA to count text.

Therefore, below portion in Nebu's formula.
"COUNTA(IF(TRIM($A$3:$A$18)=TRIM($A3),$A$3:$A$18))"

THANK YOU for the detailed explanation! Every little bit helps this...
=IFERROR("I am an Excel Ninja","Error in calculation, you are still a novice !")