# identifying alphabetic sequence, help with nested IFS or new solution?

#### AVRAHAMROOS

##### New Member
Given a column of random letters, I want to identify sequences of a minimum of 3 consecutive letters from the alphabet. For example, if the following letters are given in a column: a,b,c,x, l,m,n, p,g,m,n,o,p,p,g (series1) or a,b,c, k, l,m,n,p,g,m,n,o,p,p,g (series2), I want to get TRUE for the letters in bold.

I have created a complicated nested IF formula for cell 3 which I copy with autofill downwards in the column (I deal with cells 1 and 2 differently):
=IF(C3="","",((IF(CODE(C5)=(CODE(C4)+1),IF(CODE(C4)=CODE(C3)+1,TRUE,(IF(CODE(C2)=CODE(C1)+1,FALSE))),(IF(CODE(C3)=(CODE(C2)+1),IF(CODE(C4)=(CODE(C3)+1),TRUE,IF(CODE(C2)=CODE(C1)+1,TRUE,FALSE)))))))

The formula works (!), except in cases where two series meet or are separated by one unrelated character. In series 1 "x" (cell 4) is marked TRUE (while this should be FALSE) and in series 2 "c" (cell 3) is marked FALSE while this should be TRUE.

My questions:
1. What can I do to solve this?
2. Is there maybe a much easier way to achieve the desired result?

THANK YOU!!

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
AVRAHAMROOS,

Still a lengthy formula but maybe this will do it...
Excel Workbook
AB
3aTRUE
4bTRUE
5cTRUE
6kTRUE
7lTRUE
8mTRUE
9nTRUE
10pFALSE
11gFALSE
12mTRUE
13nTRUE
14oTRUE
15pTRUE
16pFALSE
17gFALSE
Sheet1

Hope that helps.

AMAZING. Thank you SO much!
I have received a lot of help these weeks from friendly people on this forum. Thanks to them I have managed to build a complicated tool with excel which will help me with my research. This was the last problem I had to solve. My tool is ready now. Thank you all very much!

You are welcome. It's satisfying to be able to help. Good luck with your project.

Brilliant. For me as an engineer, an unexpected application of Excel. Just shows how useful and adaptable Excel is.

Replies
6
Views
273
Replies
21
Views
255
Replies
3
Views
255
Replies
1
Views
115
Replies
1
Views
51

1,196,279
Messages
6,014,423
Members
441,818
Latest member
itsfaisalkhalid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back