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

[mgerner]

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

 

[JoeMo]

Will the format of your cells always be space-delimited sub strings? If yes, this should do what you want. Be sure to select all the cells you want to modify first, then run the macro.

Sub DeleteBtwFirstnSecondSpace()
'select cells first then run this macro
Dim c As Range, S As Variant
Application.ScreenUpdating = False
For Each c In Selection
    S = Split(c.Value, " ")
    If UBound(S) > 0 Then
        c.Value = Replace(c.Value, S(1) & " ", "")
    End If
Next c
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Another option, using you original equation

Sub DeleteWord()
Selection.Value = Evaluate(Replace("if({1},CONCATENATE(LEFT(@,(FIND("" "",@,1)-1)),"" "",MID(@,FIND("" "",@,FIND("" "",@)+1)+1,256)),@)", "@", Selection.Address))
End Sub

 

[mgerner]

This works perfectly. Thank you!! This is going to save me so much time! They will always be space-delimited strings

 

[JoeMo]

This works perfectly. Thank you!! This is going to save me so much time! They will always be space-delimited strings

You are welcome - glad we could help.