string function 2

[dstiefe]

I have a cell that contains the following "Email: (e-mail address removed)"

how do I delete the email and semicolon and move the actual email address al
the way to the left of the cell

so I would be deleting the email word

thank you

 

[Gary Keramidas]

not sure if this is what you want, but if your data is in A1, try either of
these:

Sub test()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
With ws
.Range("A1").Value = Replace(.Range("A1").Value, "Email: ", "")
End With
End Sub


Sub test2()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
With ws
..Range("A1").Value = Right(.Range("a1"), Len(.Range("a1")) - InStr(1, _
.Range("A1"), ":") - 1)
End With
End Sub

 

[Anant Basant]

You can use the following code:

Sub callFunction()
Dim str As String
str = "Email: (e-mail address removed)"
MsgBox RemoveEmail(str)

End Sub

Function RemoveEmail(strEmailStr As String)
RemoveEmail = Trim(Right(strEmailStr, Len(strEmailStr) -
Application.Find(":", strEmailStr)))
End Function

 

[JLGWhiz]

Not knowing if the space will be after the colon or how many spaces might be
there, maybe this would work. Assume the data is in cell A1.

Sub getURL()
Range("A1").Characters(1, 6).Delete
Range("A1") = Trim(Range("A1"))
End Sub