string function 2

D

dstiefe

I have a cell that contains the following "Email: [email protected]"

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
 
G

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
 
A

Anant Basant

You can use the following code:

Sub callFunction()
Dim str As String
str = "Email: [email protected]"
MsgBox RemoveEmail(str)

End Sub

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

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
 
Top