UDF for MID for "-" string & some criterias

[Bhawani]

Hi All,

I am unable to search out a for creating a UDF in attached file with below criteria :

1. if "-" count =1 in cell range then output should be cell value as it is
2. if "-" count >=2 in cell range then mid between first two "-"
3. if value in cell range contains "--" (double minus sign consecutively) then output should be wrong entry
4. if value in cell range does not contain "-" then UDF should give check entry

I have given a try but unable to reach result. please help me out.

Regards,

 

[Luke M]

Here you go.

Function MIDPART(myString As String, Optional mySym As String) As String
Dim symCount As Long
Dim newStr As String
'mySym = what to look for
'Defaults to a dash if not specified
If mySym = "" Then mySym = "-"

symCount = Len(myString) - Len(Replace(myString, mySym, ""))

Select Case symCount
    Case 0
        MIDPART = "check entry"
    Case 1
        MIDPART = myString
    Case Else
        newStr = Trim(Mid(Replace(myString, mySym, WorksheetFunction.Rept(" ", 999)), 999, 999))
        If newStr = "" Then
            MIDPART = "wrong entry"
        Else
            MIDPART = newStr
        End If
End Select

End Function

 

[Bhawani]

Thanks Luke, You make my night for sleep now it was not actually letting me sleep. Perhaps one thing i have noticed in UDF that your first if condition is not closed with End If see below :

'Defaults to a dash if not specified
If mySym = "" Then mySym = "-"
symCount = Len(myString) - Len(Replace(myString, mySym, ""))

please make me clear so that i could go 4 sweet dreams

 

[NARAYANK991]

Hi Bhawani ,

An IF statement requires a closing ENDIF only if it is over more than one line.

The following 2 pieces of code are absolutely equivalent :

Public Sub OneIf()
           num = 4
           If (num Mod 2) = 0 Then MsgBox "The number is even" Else MsgBox "The number is odd"
           num = 5
           If (num Mod 2) = 0 Then MsgBox "The number is even" Else MsgBox "The number is odd"
End Sub

Public Sub AnotherIf()
           num = 4
           If (num Mod 2) = 0 Then
              MsgBox "The number is even"
           Else
              MsgBox "The number is odd"
           End If
           num = 5
           If (num Mod 2) = 0 Then
              MsgBox "The number is even"
           Else
              MsgBox "The number is odd"
           End If
End Sub

Narayan

 

[Bhawani]

Thanks Narayan,

Now its clear for me
end if is not require when we pass if condition with in line.



Regards,

 

[Jake Collins]

Why use a UDF and not an excel formula like

=IF(IFERROR(FIND("--",K8),0)<>0,"wrong entry",IF(IFERROR(FIND("-",K8),0)=0,"check entry",IF(IFERROR(SEARCH("-",MID(K8,SEARCH("-",K8)+1,LEN(K8)-SEARCH("-",K8)+1)),0)=0,K8,MID(K8,SEARCH("-",K8)+1,SEARCH("-",MID(K8,SEARCH("-",K8)+1,LEN(K8)-SEARCH("-",K8)))-1))))

 

[Bhawani]

@ Jake collins - because it will save my time and mind both.

simply it will take more time to type excel funcation with these manu funcations and also i was using same function you told me.

so just to get rid of repeatative function typing