CODEWITHSUNDEEP
excelexcel-formula
Myra
Myra

Reputation: 77

Searching comma-separated numbers within a cell

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?

Upvotes: 1

Views: 16760

Answers (5)

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

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.

Upvotes: 2

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

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

Upvotes: 0

sabhareesh
sabhareesh

Reputation: 334

=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)

Upvotes: 0

chishaku
chishaku

Reputation: 4643

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)

Upvotes: 0

Zin Win Htet
Zin Win Htet

Reputation: 2565

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;
    }

Upvotes: -1

Related Questions