How to remove text from a cell in vba

[Macro_Nerd99]

If I had a cell that has an address stated like this:

Number: 1921 Street name: Main st.

How do I remove the text "Number:" and the text "Street name:" and the space in between, so It just has the address: "1921 Main st."
I've been trying to use the split function in other cells, but this one has 2 colons so it's hard.


Thanks

 

[Z51]

You could obviously run this against a range of values, not just cell A1.

Sub macro_nerd()
With Range("A1")
    .Value = Replace(Replace(.Value, "Number: ", ""), "Street name: ", "")
End With
End Sub

 

[steve the fish]

You could just do a replace

With Sheets("Sheet1")
    .Columns(1).Replace "Number: ", ""
    .Columns(1).Replace "Street name: ", ""
End With