Excel Function or VBA code to search for number in comma-separated string

Question

In a column that contains comma separated numbers entered by users:

1,2,3,4,5
11,12,14,3
21,32,45,92,101,1
100,234,125,5

I need to search for a specific number in each list, for example "1" and return TRUE in a Conditional Formatting rule to highlight that cell. However, everything I've tried returns true for any occurrence of the number such as 11, 12, 21, 100 and so on not just 1. I need it to only return TRUE when a specific number between the commas exists.

encapsulate each string with commas, then search for **,1,** — Gary's Student, Jul 11 '17 at 03:14

score 2 · Accepted Answer · answered Jul 11 '17 at 08:22

2

I need it to only return TRUE when a specific number between the commas exists.

You can create UDF as below

Function CheckOne(rng As Range, chkValue As Long) As Boolean
    Dim n
    For Each n In Split(rng.Value, ",")
        If CLng(n) = chkValue Then
            CheckOne = True
            Exit For
        End If
    Next n
End Function

See image for reference.

For details on UDF see this.

answered Jul 11 '17 at 08:22

Mrig

11,612
2
13
27

I saw this on a few places throughout the interweb but I couldn't figure out how to make it work for our needs. Not sure what I'm doing wrong when I try to implement it. – RogueVeggie Jul 14 '17 at 04:31
1

Thank you very much. I figured out what I was doing wrong. Basically I was being and trying not to fill in another cell to get my results. – RogueVeggie Jul 14 '17 at 05:08

score 0 · Answer 2 · answered Jul 11 '17 at 02:45

Try something like the code below (explanation inside the code comments):

Option Explicit

Sub HighlightOnes()

Dim i As Long
Dim Elem As Variant
Dim myArr As Variant

With Worksheets("Sheet1") ' change "Sheet1" to your hseet's name
    For i = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row ' loop until last row with data in column A
        myArr = Split(.Range("A" & i).Value, ",")

        ' loop through array elements and look for 1
        For Each Elem In myArr
            If Elem Like "1" Then ' check if current element is "1"
                .Range("B" & i).Value = "True" ' put a value of "True" in the next column (just for example)
                ' or proceed here with the formatting of the cell 
                Exit For
            End If
        Next Elem

    Next i
End With

End Sub

Does the value "True" need to be put into another column for this to work? Is there a way to do it without that step? — RogueVeggie, Jul 14 '17 at 04:30

Excel Function or VBA code to search for number in comma-separated string

2 Answers2

Linked