Excel insists on adding character to formula

P

Pierre

Have a formula

What I would like: ,IF(COUNTIF(A1,"*"&{.124}&"*"),"yes"

Excel "insists on placing a zero" before the dot in front of the 124

What it changes to by pressing enter:
,IF(COUNTIF(A1,"*"&{0.124}&"*"),"yes"

I need the dot in the formula as to only check if the "124"
immediately follows the dot, and not a 124 which may occur someplace
within the cells contents. If I leave the zero in the formula, if
does not return the correct answer.

Is there a way around this apparent quirk?

Thank you.
pierre
 
T

T. Valko

What is in the cell that might precede the ".124"?

If it's a number like 1.124 then the wildcards won't work. Wildcards don't
work on numbers, they only work on text.

If it's a text string like A.124 then try this:

IF(COUNTIF(A1,"*.124*"),"yes"

The squiggly brackets { } denote an array constant. Since you're only using
a single criteria they're not needed.
 
P

Pierre

What is in the cell that might precede the ".124"?

If it's a number like 1.124 then the wildcards won't work. Wildcards don't
work on numbers, they only work on text.

If it's a text string like A.124 then try this:

IF(COUNTIF(A1,"*.124*"),"yes"

The squiggly brackets { } denote an array constant. Since you're only using
a single criteria they're not needed.

--
Biff
Microsoft Excel MVP












- Show quoted text -

Makes sense. . .Thank you, I'll try it in the AM, and report back.

Pierre
 
T

T. Valko

Since you have 2 criteria we can use the array constant again:

=IF(OR(COUNTIF(A1,"*"&{".124",".125"}&"*")),"Yes","No")

--
Biff
Microsoft Excel MVP


Biff:

This did the trick; the data to be analyzed is text, and the source
data looks something like: P00345.124ELE145


As it stands, I'm at the upper limit of IF statements, and could use
one more, so I was wondering if I could combine into a single
statement searching for:
..124, and .125?

Right now, it looks like this, and works fine:
IF(COUNTIF(A4,"*.124*"),"Yes",IF(COUNTIF(A4,"*.125*"),"Yes","")))


Would it be something like =IF(OR(COUNTIF(A4,"*.124,.125*"),"Yes"))
(It doesn't work, but I don't think it's phrased properly.

Many thanks again, for your help and feedback.

Pierre
 
T

T. Valko

Or, this version which is a few keystrokes shorter:

=IF(OR(COUNTIF(A1,"*.12"&{4,5}&"*")),"Yes","No")
 
T

T. Valko

Now, where do you wnat those pizzas sent?

Make 'em with thick crust, mushrooms, peppers, tomatoes and xtra cheese!

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Awesome!!!

Now, where do you wnat those pizzas sent?

Pierre
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top