August 14, 2010

Trim string from sentence,Use of Trim in different Ways in excel

Trim string from sentence in excel


 Public Function trimtwo(ByVal r As Range) As String  
 Dim str As String  
 str = r(1, 1)  
 Dim Flag As Boolean  
 Flag = False  
 Dim indexof As Integer  
 indexof = InStr(str, "Shri")  
 If indexof Then  
 str = Mid(str, indexof + 4, Len(str))  
 Flag = True  
 End If  
 indexof = InStr(str, "SHRI")  
 If indexof Then  
 str = Mid(str, indexof + 4, Len(str))  
 Flag = True  
 End If  
 indexof = InStr(str, "Smt.")  
 If indexof Then  
 'str = Mid(str, 1, 1) & Mid(str, indexof + 5, Len(str))  
 str = Mid(str, indexof + 5, Len(str))  
 Flag = True  
 End If  
 indexof = InStr(str, ",")  
 If indexof Then  
 str = Mid(str, 1, indexof - 1)  
 End If  
 str = Trim(str)  
 If Flag Then  
 indexof = InStr(str, " ")  
 If indexof Then  
 str = Mid(str, 1, indexof - 1) & Mid(str, indexof + 1, Len(str))  
 End If  
 End If  
 trimtwo = str  
 End Function  
 Public Function trimone(ByVal r As Range) As String  
 Dim str As String  
 str = Mid(r(1, 1), 1, 1)  
 trimone = str  
 End Function  
 Use of Trim in excel in different ways  
 Public Function trimone(ByVal r As Range) As String  
 Dim str As String  
 str = Trim(Mid(r(1, 1), 1, 1))  
 trimone = str  
 End Function  
 Public Function trims(ByVal r As Range) As String  
 Dim str As String  
 str = r(1, 1)  
 Dim Flag As Boolean  
 Flag = False  
 Dim indexof As Integer  
 str = Trim(str)  
 str = WorksheetFunction.Substitute(str, ". ", ".")  
 str = Trim(str)  
 indexof = InStr(str, "shri")  
 If indexof = 1 Then  
 str = WorksheetFunction.Substitute(str, "shri", "")  
 End If  
 str = Trim(str)  
 indexof = InStr(str, "Shri")  
 If indexof = 1 Then  
 str = WorksheetFunction.Substitute(str, "Shri", "")  
 End If  
 str = Trim(str)  
 indexof = InStr(str, "SHRI")  
 If indexof = 1 Then  
 str = WorksheetFunction.Substitute(str, "SHRI", "")  
 End If  
 str = Trim(str)  
 indexof = InStr(str, "Smt")  
 If indexof = 1 Then  
 str = WorksheetFunction.Substitute(str, "Smt", "")  
 End If  
 str = Trim(str)  
 indexof = InStr(str, "SMT")  
 If indexof = 1 Then  
 str = WorksheetFunction.Substitute(str, "SMT", "")  
 End If  
 str = Trim(str)  
 indexof = InStr(str, "Shree")  
 If indexof = 1 Then  
 str = WorksheetFunction.Substitute(str, "Shree", "")  
 End If  
 str = Trim(str)  
 indexof = InStr(str, "Mr")  
 If indexof = 1 Then  
 str = WorksheetFunction.Substitute(str, "Mr", "")  
 End If  
 str = Trim(str)  
 indexof = InStr(str, "KUM")  
 If indexof = 1 Then  
 str = WorksheetFunction.Substitute(str, "KUM", "")  
 End If  
 str = Trim(str)  
 indexof = InStr(str, "ben")  
 If indexof Then  
 str = WorksheetFunction.Substitute(str, "ben", "")  
 End If  
 str = Trim(str)  
 indexof = InStr(str, "BEN")  
 If indexof Then  
 str = WorksheetFunction.Substitute(str, "BEN", "")  
 End If  
 str = Trim(str)  
 indexof = InStrRev(str, ",")  
 If indexof Then  
 str = Mid(str, 1, indexof - 1)  
 End If  
 str = Trim(str)  
 str = WorksheetFunction.Substitute(str, ",", "")  
 str = Trim(str)  
 indexof = InStr(str, ".")  
 If indexof = 1 Then  
 str = Mid(str, indexof + 1, Len(str))  
 End If  
 str = Trim(str)  
 indexof = InStrRev(str, ".")  
 If indexof = Len(str) Then  
 str = Mid(str, 1, Len(str) - 1)  
 End If  
 str = Trim(str)  
 trims = str