Excel Formula which can Extract Specific Word if Repeat

Question

I asked a question and I found the answer before but now I have similar change on my question.

Check My Old Question Here

Answer of my Old Question.

=IFERROR(IF(LEN(A2)-FIND("463",A2)>=9,0&MID(A2,FIND("463",A2),10),"Not Completed"),"Not Available")

I want to extract contact numbers inside a cell between some other numbers as before. But now I want a formula like this "If 463 Available in Two Times inside A2 then Target 2nd 463 and If Seven Characters on Right Side of 2nd 463 Available then Call 463 and Add Zero Before it and Add its Seven Characters at end.

• If 463 Not Available then show Not Available

• If Seven Characters Not Available after 2nd 463 then show Not Completed

• If 463 is not two times then show Not Repeating.

Data

3498114632030374637348901    
9856979165879564897347    
8978944635618978946341897
3498114632030377348901 

Results

04637348901    
Not Available    
Not Completed
Not Repeating

Please let me know the formula if it is possible.

Answer 1

I think this formula should do it. It probably can be simplified - e.g. you could just change all the LEN("463") to 3. It's just a few nested IFs:

1. If you can't find "463", return "Not Available": IF(ISERROR(FIND("463",A1)),"Not Available"
2. If you can't find a second occurrence of "463," return "Not Repeating": IF(ISERROR(FIND("463",A1,FIND("463",A1)+LEN("463"))),"Not Repeating"
3. If there aren't 7 characters after the second occurrence of "463", return "Not Completed": IF(LEN(A1)-(FIND("463",A1,FIND("463",A1)+LEN("463"))+LEN("463"))<7,"Not Completed"
4. Else, return "0" and the second occurrence of "463" and the 7 following characters: 0&MID(A1,FIND("463",A1,FIND("463",A1)+LEN("463")),10)

---

 =IF(ISERROR(FIND("463",A1)),"Not Available",IF(ISERROR(FIND("463",A1,FIND("463",A1)+LEN("463"))),"Not Repeating",IF(LEN(A1)-(FIND("463",A1,FIND("463",A1)+LEN("463"))+LEN("463"))<7,"Not Completed",0&MID(A1,FIND("463",A1,FIND("463",A1)+LEN("463")),10))))

Answer 2

@BigBen answer beautified (thanks to http://excelformulabeautifier.com/):

=IF(
    ISERROR(
        FIND(
            "463",
            A1
        )
    ),
    "Not Available",
    IF(
        ISERROR(
            FIND(
                "463",
                A1,
                FIND(
                    "463",
                    A1
                ) +
                LEN(
                    "463"
                )
            )
        ),
        "Not Repeating",
        IF(
            LEN(
                A1
            ) - (                FIND(
                    "463",
                    A1,
                    FIND(
                        "463",
                        A1
                    ) +
                    LEN(
                        "463"
                    )
                ) +
                LEN(
                    "463"
                ) ) < 7,
            "Not Completed",
            0 &
            MID(
                A1,
                FIND(
                    "463",
                    A1,
                    FIND(
                        "463",
                        A1
                    ) +
                    LEN(
                        "463"
                    )
                ),
                10
            )
        )
    )
)