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

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!

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

Identify is a little vague. Are you trying to aggregate based on finding CU
in a field. If so then check out this link...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Check out example 9 at the bottom.

If you just want to know if a cell is contains CU then you can use Find or
Search. Look them up in help.
--
HTH...

Jim Thomlinson


"pa1971" wrote:

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!

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

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!

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

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


"Jim Cone" wrote:



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!

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

pa1971 wrote...
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?

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*",YourRangeAddressH ere)),
ROW(YourRangeAddressHere)-MIN(ROW(YourRangeAddressHere))+1),n)