Wild card search in CF

[Darren]

Trying to conditionally format cells that contain values such as "A3" and "C7".
But can't sort out the formula properly. I can get "=A*" to find the A3 but
how can I formulate something like "=[A-Z]*" to find all occurrences?
Many thanks
Darren

 

[CyberTaz]

For clarification - it sounds like you're trying to select cells that
contain *formulas*, not values. If so, you're using the wrong feature

Use Edit> Go To, click the Special... button, select Formulas (or one of the
appropriate options such as Dependents), then click OK.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Darren]

Thanks for the prompt response Bob.
I'm trying to use the new Conditional Formatting feature and all the cells
contain values only.
I understand that the formula option of CF works the same way as range
conditions such as countif(myrange, "=A*").
The question is what syntax should I use to select range of characters
rather than just those beginning with letter A in the above example.
Cheers
Darren

 

[CyberTaz]

Hi Darren -

Well, I'm afraid I'm not at all clear on what you're trying to do. Sorry if
I seem dense, but without seeing a more vivid example of what you're working
with & what your objective is I'm at a complete loss.

If you are trying to have a variety of cells examined - whose content might
be anything - and have CF applied only to those which contain values that
appear to be cell references I regret that I wouldn't know where to begin.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Darren]

This is really just a question of wildcard syntax for use in functions such
as countif or sumif or CF etc. Nothing complicated such as direct or indirect
references to cells.

In some languages it is permitted to say "[A-D]?" in order to find 2
character values beginning with the letter A or B or C or D.

What is a comparable syntax in Excel please?

Darren

 

[JE McGimpsey]

This is really just a question of wildcard syntax for use in functions such
as countif or sumif or CF etc. Nothing complicated such as direct or indirect
references to cells.

In some languages it is permitted to say "[A-D]?" in order to find 2
character values beginning with the letter A or B or C or D.

What is a comparable syntax in Excel please?

There is no comparable syntax in XL. Instead you need to combine two or
more tests.

The easiest way to meet the specification above, using XL functions, is
probably:

=SUMPRODUCT(--(LEN(rng)=2),--(rng>="A"),--(rng<"E"))

Note that, through XL04, rng cannot be an entire column, nor can it be
two-dimensional.

You could instead create a User Defined Function that would allow you to
use VBA's 'Like' comparison operator:

Public Function CountLike( _
ByRef rng As Excel.Range, _
ByVal regexp As String) As Variant
Dim rArea As Range
Dim rCell As Range
Dim nSum As Long
On Error GoTo ErrorHandler
For Each rArea In rng.Areas
For Each rCell In rArea
nSum = nSum - (rCell.Text Like regexp)
Next rCell
Next rArea
CountLike = nSum
Exit Function
ErrorHandler:
CountLike = CVErr(xlErrValue)
End Function

which you could then call as

=CountLike(A1:Z100, "[A-D]?")

 

[Darren]

Brilliant - Many thanks.
Darren