I'm using the following formula to return everything in the referenced cell except the text between the first and second space. The word b/w the first and second space has to be taken out. I have several hundred cells that I need to apply this to in different workbooks.

=CONCATENATE(LEFT(A1,(FIND(" ",A1,1)-1))," ",MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,256))

I was wanting to be able to select a range of cells on the sheet that I need to apply this formula to, and then have a macro loop through all the cells that I have selected and make the change.

For example,

If a cell has "John Smith California Unites States", I need it to change to "John California United States"

It will always be the word between the first and second space that I need to take out.


I started to do something like below, but I don't know if this is the right approach or not...Any help would be greatly appreciated. 


Sub deleteword()

Dim rng As Range, cell As Range
Set rng = selection
For Each cell In rng

ActiveCell.Formula = ???

Next cell

End Sub