Is there anything like the Access "LIKE" function in Excel?

P

pa1971

I would like to identify only the values in a spreadsheet with "CU" included
in them. That "CU" could be at the beginning, end or middle of the values in
the column. In access I could use the LIKE feature. Is there anything
similar in Excel to identify them?

Thank you!
 
J

Jim Cone

The "Like" operator is available only when using VBA code in Excel.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"pa1971"
wrote in message
I would like to identify only the values in a spreadsheet with "CU" included
in them. That "CU" could be at the beginning, end or middle of the values in
the column. In access I could use the LIKE feature. Is there anything
similar in Excel to identify them?

Thank you!
 
J

JLatham

Here's some code that expands on Jim's comment that you could use to identify
the cells. Presumes that you aren't shading any cells red for other reasons.
When it finds a cell with cu, Cu, CU or cU in it anywhere, it'll shade the
cell bright red.

The clear shading routine clears all cells of their shading - so it
will/would wipe out any other shading you have applied to other cells.

Both routines can be 'tweaked' to give more desirable results.

Sub FindCU_Entries()
Dim anyCell As Range

For Each anyCell In ActiveSheet.UsedRange
If UCase(anyCell.Text) Like "*CU*" Then
anyCell.Interior.ColorIndex = 3
End If
Next
End Sub

Sub ClearColorIndex()
'resets ALL sheet cell shading to none/white
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
End Sub
 
H

Harlan Grove

pa1971 said:
I would like to identify only the values in a spreadsheet with "CU"
included in them. That "CU" could be at the beginning, end or middle
of the values in the column. In access I could use the LIKE feature.
Is there anything similar in Excel to identify them?
Click to expand...

Count them? Use

=COUNTIF(YourRangeAddressHere,"*CU*")

Find the index of the first one in a 1D range? Use

=MATCH("*CU*",YourRangeAddressHere,0)

Find the index of the n_th (n > 1) one in a 1D range? Use the array
formula

=SMALL(IF(ISNUMBER(SEARCH("*CU*",YourRangeAddressHere)),
ROW(YourRangeAddressHere)-MIN(ROW(YourRangeAddressHere))+1),n)
 
Top