Hacker News Reader

Will it give us beauts like this?:

=IFERROR(IF(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(SEARCH("Banner",AC5),SEARCH("EBL2",AC5)),SEARCH("Movie Art",AC5)),SEARCH("Use as is",AC5)),SEARCH("TTT",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1)<IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(SEARCH("Banner",AC5),SEARCH("EBL2",AC5)),SEARCH("Movie Art",AC5)),SEARCH("Use as is",AC5)),SEARCH("TTT",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1),MID(AC5,IFERROR(IFERROR(IFERROR(SEARCH("Customs",AC5),SEARCH("Custom",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1),IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(SEARCH("Banner",AC5),SEARCH("EBL2",AC5)),SEARCH("Movie Art",AC5)),SEARCH("Use as is",AC5)),SEARCH("TTT",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1)-IFERROR(IFERROR(IFERROR(SEARCH("Customs",AC5),SEARCH("Custom",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1)),MID(AC5,IFERROR(IFERROR(IFERROR(SEARCH("Customs",AC5),SEARCH("Custom",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1),IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(SEARCH("Banner",AC5),SEARCH("EBL2",AC5)),SEARCH("Movie Art",AC5)),SEARCH("Use as is",AC5)),SEARCH("TTT",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1)-IFERROR(IFERROR(IFERROR(SEARCH("Customs",AC5),SEARCH("Custom",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1))),"")

(source: https://www.quora.com/What-is-the-longest-excel-formula-you-... )

View on HN

Function extractText(cell As Range) As String ' Declare an array of search terms Dim searchTerms As Variant searchTerms = Array("Banner", "EBL2", "Movie Art", "Use as is", "TTT", "Generic") ' Initialize start and end positions to 0 Dim startPos As Long startPos = 0 Dim endPos As Long endPos = 0 ' Loop through search terms and find the first occurrence of any of them in the cell For Each searchTerm In searchTerms startPos = WorksheetFunction.Search(searchTerm, cell) If startPos > 0 Then Exit For End If Next searchTerm ' If none of the search terms were found, try finding "Customs" or "Custom" If startPos = 0 Then startPos = WorksheetFunction.Search("Customs", cell) If startPos = 0 Then startPos = WorksheetFunction.Search("Custom", cell) End If End If ' If none of the above were found, try finding "Generic" If startPos = 0 Then startPos = WorksheetFunction.Search("Generic", cell) End If ' If none of the search terms were found, return an empty string If startPos = 0 Then extractText = "" Else ' Loop through search terms and find the next occurrence of any of them after the start position For Each searchTerm In searchTerms endPos = WorksheetFunction.Search(searchTerm, cell, startPos + 1) If endPos > 0 Then Exit For End If Next searchTerm ' If none of the search terms were found after the start position, set the end position to the end of the cell If endPos = 0 Then endPos = Len(cell) + 1 End If ' Extract the text between the start and end positions extractText = Mid(cell, startPos, endPos - startPos) End If End Function