Excel - delete 4th word in every column

Question

How can I delete the fourth word in every column in an excel spreadsheet? For example, I have "Mr. and Mrs Smith". I want to keep Mr. and Mrs. but delete Smith. Isn't there a way to find and delete the 4th word? Thanks so much for any help...

Answer 1

This is kind of a workaround, but it will work if you're just dealing with regular text (not formulas).

Let's say your sheet with data on it is called "Sheet1". Create a new worksheet on your spreadsheet (let's assume it's called "Sheet2"), and in A1, type:

=IFERROR(LEFT(Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1)+1)+1)-1),"")

If you want to delete only the fourth word, type:

=IFERROR(LEFT(Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1)+1)+1))&RIGHT(Sheet1!A1,LEN(Sheet1!A1)-FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1)+1)+1)+1)),IFERROR(LEFT(Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1,FIND(" ",Sheet1!A1)+1)+1)-1),""))

Copy cell A1 and paste it in the cells on Sheet2 that match up with cells on Sheet1 with the data you want to work with. (So if you have data on Sheet1 in cells A1 through D5, paste it on Sheet2 in cells A1 through D5.) Copy all those cells you pasted, switch to Sheet1, and paste values at the matching spots. You can now delete Sheet2.

Answer 2

This might be an off the wall idea, but try making the file into a space delimited file, then open it with excel. Now delete the 4th column. This sounds good on paper... Other than this, I don't know.

Answer 3

Here's a more elegant/efficient way to do it, if you're okay to paste in a Visual Basic macro:

Let's say your sheet with data on it is called "Sheet1".

1. On the Developer tab, click Macros. (If there's no Developer tab, click the big round Microsoft Office Button in the top-left of the window, click Excel Options, then click Popular, and then click the Show Developer tab in the Ribbon check box).
2. A dialog box will pop up. In the Macro name: textbox, name your macro; something like DeleteFourthWord will work.
3. Click the Create button. (If the Create button is still disabled, take out spaces and punctuation from your macro name.)

4. Paste the following in the window that comes up, in the space between "Sub DeleteFourthWord()" and "End Sub":

    For Each c In ActiveCell.CurrentRegion.Cells
           If c.HasFormula = False Then
               Original_Cell_Text = c.Value
               Text_To_Parse = Original_Cell_Text
               Word1 = Left(Text_To_Parse, InStr(1, Text_To_Parse, " ", vbTextCompare))
               If Len(Word1) = 0 Then
                   Word1 = Text_To_Parse
                   Text_To_Parse = ""
               Else
                   Text_To_Parse = Right(Text_To_Parse, Len(Text_To_Parse) - Len(Word1))
               End If
               Word2 = Left(Text_To_Parse, InStr(1, Text_To_Parse, " ", vbTextCompare))
               If Len(Word2) = 0 Then
                   Word2 = Text_To_Parse
                   Text_To_Parse = ""
               Else
                   Text_To_Parse = Right(Text_To_Parse, Len(Text_To_Parse) - Len(Word2))
               End If
               Word3 = Left(Text_To_Parse, InStr(1, Text_To_Parse, " ", vbTextCompare))
               If Len(Word3) = 0 Then
                   Word3 = Text_To_Parse
                   Text_To_Parse = ""
               Else
                   Text_To_Parse = Right(Text_To_Parse, Len(Text_To_Parse) - Len(Word3))
               End If
               Word4 = Left(Text_To_Parse, InStr(1, Text_To_Parse, " ", vbTextCompare))
               If Len(Word4) = 0 Then
                   Word4 = Text_To_Parse
                   Text_To_Parse = ""
               Else
                   Text_To_Parse = Right(Text_To_Parse, Len(Text_To_Parse) - Len(Word4))
               End If
               Remaining_Text = Text_To_Parse
               If (Len(Word1) > 0 And Len(Word2) > 0 And Len(Word3) > 0 And Len(Word4) > 0) Then
                   If Len(Remaining_Text) > 0 Then
                       c.Value = Word1 + Word2 + Word3 + Remaining_Text
                   Else
                       c.Value = Word1 + Word2 + Word3
                   End If
               End If
           End If
       Next
   

5. Close the Microsoft Visual Basic window and click Macros on the Developer tab.

6. Select the macro you just created and click the Options... button.
7. Under Shortcut key, pick a shortcut key to use for this function (I'll use the "t" key in this example) and type it there.
8. Push the OK button and then close the Macro dialog box.
9. Select the cells you want to remove the fourth word from and push your shortcut key (like Ctrl+t). You can repeat this step as many times as you want.

---

Note: If you want to delete everything after the third word, paste this instead:

For Each c In ActiveCell.CurrentRegion.Cells
    If c.HasFormula = False Then
        Original_Cell_Text = c.Value
        Text_To_Parse = Original_Cell_Text
        Word1 = Left(Text_To_Parse, InStr(1, Text_To_Parse, " ", vbTextCompare))
        If Len(Word1) = 0 Then
            Word1 = Text_To_Parse
            Text_To_Parse = ""
        Else
            Text_To_Parse = Right(Text_To_Parse, Len(Text_To_Parse) - Len(Word1))
        End If
        Word2 = Left(Text_To_Parse, InStr(1, Text_To_Parse, " ", vbTextCompare))
        If Len(Word2) = 0 Then
            Word2 = Text_To_Parse
            Text_To_Parse = ""
        Else
            Text_To_Parse = Right(Text_To_Parse, Len(Text_To_Parse) - Len(Word2))
        End If
        Word3 = Left(Text_To_Parse, InStr(1, Text_To_Parse, " ", vbTextCompare))
        If Len(Word3) = 0 Then
            Word3 = Text_To_Parse
            Text_To_Parse = ""
        Else
            Text_To_Parse = Right(Text_To_Parse, Len(Text_To_Parse) - Len(Word3))
        End If
        Remaining_Text = Text_To_Parse
        If (Len(Word1) > 0 And Len(Word2) > 0 And Len(Word3) > 0) Then
            c.Value = Word1 + Word2 + Word3
        End If
    End If
Next

---

(Source Note: I modified the instructions on how to get the Developer tab from the Excel Help file.)