I am using the countif function on a column of numbers. I want to count how many times a number was used in a very large list. However, some of the numbers I am checking against the list have an asterisk (*) in them. For example, I have 4587 and 458*7. I would only want 4587 without the asterisk to show as having some matches, but both numbers come up with the same result. Is there a way to count the asterisk as a regular character instead of an wild card?
Last edited by bel99004; 11-17-2016 at 05:41 PM. Reason: Solved
Can you attach a sample book ?
Or at least the formula you currently have
It's unclear where exactly the * exists, in the formula or in the data ? Or both?
I've tried to attach a workbook to make things more clear. Hopefully it will work.
Try
=COUNTIF(JointList,SUBSTITUTE(A3,"*","~*"))
The substitute replaces the * with ~*
The ~ is a flag to excel that the next character is treated literally.
Bingo! That worked! I knew how to find and replace using the tilde to ignore the wildcard aspect, but hadn't thought to include it in the function using Substitute. Brilliant!
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks