Searching comma-separated numbers within a cell

Question

I have a column with many comma-separated numbers, e.g.:

   100633,101

    123,12

   100633,1000

How do I search whether a particular cell contains the number 100633?

Check http://stackoverflow.com/questions/18617175/excel-check-if-a-cell-contains-a-substring — Vojtěch Dohnal, Sep 09 '14 at 04:54
@VDohnal: That's also what I thought at first, but a simple string search won't always work: it will erroneously return `TRUE` for e.g. `1,2,3,999100633999`. Gotta look at the entire comma-separated value. — Jean-François Corbett, Sep 09 '14 at 07:25
Are there always only two numbers? Or is it sometimes a longer list? Is `100633` always first in the list, when it does occur? — Jean-François Corbett, Sep 09 '14 at 07:40
@Jean-FrançoisCorbett You are right but I cannot believe that such a simple task cannot be accomplished without using VBA. — Vojtěch Dohnal, Sep 09 '14 at 08:40

score 2 · Accepted Answer · edited May 23 '17 at 11:46

2

In response to @Jean-François Corbett correct remark and based on the following answer

Check whether a cell contains a substring

here is a formula that searches the cell A1 for the text 100633 considering only comma separated values

=ISNUMBER(SEARCH(",100633,",","&A1&","))

It handles correctly text like 1,2,3,999100633999

You can also use FIND instead of SEARCH function. FIND is case sensitive. With SEARCH you can use wildcard characters.

edited May 23 '17 at 11:46

Community

1
1

answered Sep 09 '14 at 08:34

Vojtěch Dohnal

7,867
3
43
105

Doesn't this answer only work if "100633" is NOT FIRST in the comma-delimited list? IE, the search is for a number that STARTS WITH and ends with a comma. If it's the first number in the list, it won't start with a comma, and so will not be found? – Tom Auger Nov 04 '15 at 16:58
1

@TomAuger No, since it searches within `","&A1&","` and not within just A1. – Vojtěch Dohnal Nov 05 '15 at 07:21

chishaku · Answer 2 · 2014-09-09T05:07:20.257

0

Assuming the first target cell is in A1, returns TRUE or FALSE:

=ISNUMBER(SEARCH(100633,A1))

Custom values to return if true or false:

=IF(ISNUMBER(SEARCH(100633,A1)),'yes','no)

edited Sep 09 '14 at 05:07

answered Sep 09 '14 at 05:01

chishaku

4,577
3
25
33

1

False positive on e.g. `1,2,3,999100633999`. – Jean-François Corbett Sep 09 '14 at 07:23

sabhareesh · Answer 3 · 2014-09-09T07:48:36.503

0

=IF(FIND("100633",E11)=1,"Number Exists","Not Exists")

The formula searches for the text and returns whether it is present in the cell or not

Updated: Try Using the following excel formula:

=MID(E18,FIND("100633",E18),6)

edited Sep 09 '14 at 07:48

answered Sep 09 '14 at 05:07

sabhareesh

324
1
4

1

False positive on e.g. `1,2,3,999100633999`. – Jean-François Corbett Sep 09 '14 at 07:22
I 've tried using the following formula:=MID(E18,FIND("100633",E18),6) hope it works fine – sabhareesh Sep 09 '14 at 07:55
Well, that formula also says the number `100633` exists in that example I just gave. But clearly it doesn't. `999100633999` and `100633` are two very different numbers. – Jean-François Corbett Sep 09 '14 at 08:01

Jean-François Corbett · Answer 4 · 2014-09-09T08:03:45.813

VBA solution:

Function CommaSeparatedListContains(ByVal csv As String, ByVal v As String, _
    Optional ByVal delimiter As String = ",") As Boolean

    Dim i As Long
    Dim splitCsv() As String
    splitCsv = Split(csv, delimiter)
    CommaSeparatedListContains = False
    For i = LBound(splitCsv) To UBound(splitCsv)
        If splitCsv(i) = v Then
            CommaSeparatedListContains = True
            Exit Function
        End If
    Next i
End Function

Example usage:

=CommaSeparatedListContains(A1,100633)

enter image description here

score -1 · Answer 5 · answered Sep 09 '14 at 05:03

-1

Use the following method...

    private boolean isNumberInvolved(String column, String number_to_check){
        String[] numberArray = column.split(",");
        for(int i=0; i<numberArray.length; i++){
            if(numberArray[i].equals(number_to_check))
                return true;
        }
        return false;
    }

answered Sep 09 '14 at 05:03

Zin Win Htet

2,448
4
32
54

1

Thanks everyone ..That time I needed the solution quickly so used a very raw method...Now ,I am applying these methods. There are many correct answers for this apart for the one I checked (as answer). Bt I am not able to select all. So selected the one whose formulae I am applying . Thanks a lot again. – Myra Sep 12 '14 at 04:37

Searching comma-separated numbers within a cell

5 Answers5

Linked