CODEWITHSUNDEEP
excelexcel-formulaspreadsheetexcel-2016
Aashiq Shajahan
Aashiq Shajahan

Reputation: 55

How to find a specific data of consecutive numbers inside an excel spreadsheet column

Please help me to find sequence of numbers present in column of a spreadsheet.

I have a large data of 1's and 0's in an excel column, I need to find the sequence of consecutive 1's and 0's in the column. For example, my excel column is given below:

0
0
1
1
0
0
1
0
1
0
0
1
1
0
1
1
1
0
0

Please assume this as some of my column data, I need to find where are the sequences of 1's and 0's present in the column. For example, I need to find:

0
0
1
1
0
1

This sequence of data in the column. Is there any function for sequence search or something? Please Help!!

Upvotes: 4

Views: 675

Answers (2)

JvdV
JvdV

Reputation: 75900

In Excel 2019 and O365, it's rather easy using CONCAT():

=FIND("001101",CONCAT(A1:A19))

However, combining a range of cells prior to these versions of Excel can be tricky. You will quickly get tangled up in syntax like =MAX((A1:A19=0)*(A2:A20=0)*(A3:A21=1)*(A4:A22=1)*(A5:A23=0)*(A6:A24=1)*(ROW(A1:A19))) (as an array formula). An UDF is not a bad alternative in this case:

Function FINDSEQ(seq As String, rng as Range) As Long
    FINDSEQ = InStr(1, Join(Application.Transpose(rng.Value), ""), seq)
End Function

Call like: =FINDSEQ("001101",A1:A19)

Note - Better solutions may be found since Application.Transpose will have an upperbound limit.

Upvotes: 1

Andreas
Andreas

Reputation: 9207

If the numbers are formated as string you can simply do: =search("1 1 0 1 0 1";A1)

Upvotes: 0

Related Questions