Extract emails from a string

[Sharid]

I have this formula that extracts emails from a string in a row, this works fine.

I need this to run on a command button so when the command button is clicked, the formula is pasted into column B as main times as there are rows in column A with data

e.g column A has 200 rows of data, formula is pasted into column B 200 times,

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND("@",A1))-1)," ",REPT(" ",LEN(A1))),LEN(A1)))

If possible a paste special VALUE only of column B is pasted into column C as column B is a formula.

There is only 1 email per row

 

[Scott Huish]

Private Sub CommandButton1_Click()
Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("" "",A1&"" "",FIND(""@"",A1))-1),"" "",REPT("" "",LEN(A1))),LEN(A1)))"
End Sub

 

[Sharid]

thanks your a life saver