CODEWITHSUNDEEP
excel
Iftek Har
Iftek Har

Reputation: 161

I want an Excel Formula which can Extract Specific Word If total Character 10

I want to extract some contact numbers inside a cell between some other numbers. Wanted formula like this "If 463 Available and If Seven Characters on Right Side of 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 463 then show Not Completed

Data

3498114632030377348901

9856979165879564897347

8978945618978946341897

Results

04632030377

Not Available

Not Completed

Please let me know the formula if it is possible.

Upvotes: 1

Views: 69

Answers (3)

QHarr
QHarr

Reputation: 84465

As an UDF using Regex (I know you wanted a formula but you have excellent answers already)

Option Explicit
Public Sub test()
    Dim arr(), i As Long
    arr = Array("3498114632030377348901", "9856979165879564897347", "8978945618978946341897")

    For i = LBound(arr) To UBound(arr)
        Debug.Print ResponseFromCheck(arr(i))
    Next i
End Sub
Public Function ResponseFromCheck(ByVal inputString As String) As String
    Dim re As Object, i As Long
    Set re = CreateObject("VBScript.RegExp")
    Dim patterns(): patterns = Array("463", "463\d{7}")
    With re
        .Global = True
        .MultiLine = True
        For i = LBound(patterns) To UBound(patterns)
            .pattern = patterns(i)
            If Not .test(inputString) Then
                Select Case i
                Case 0
                    ResponseFromCheck = "Not available"
                Case 1
                    ResponseFromCheck = "Not completed"
                End Select
                Exit Function
            End If
        Next
        ResponseFromCheck = "0" & .Execute(inputString)(0)
End With
End Function

UDF used in sheet:

Response

Upvotes: 1

Alexander van Oostenrijk
Alexander van Oostenrijk

Reputation: 4774

=IF(ISNUMBER(FIND(463,A1)), IF(FIND(463,A1) + 9 > LEN(A1), "Incomplete", CONCATENATE("0463", MID(A1,FIND(463,A1)+3,7))), "Not Available")

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152605

=IFERROR(IF(LEN(A1)-FIND("463",A1)>=9,0&MID(A1,FIND("463",A1),10),"NOT COMPLETED"),"NOT AVAILABLE")

enter image description here

Edit: found a shorter version.

Upvotes: 3

Related Questions