VBA - delete text b/w first and second space in a string of text - apply to selected range

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.

Please Login or Register  to view this content.