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