Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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...

share|improve this question
    
will the beginning always be "Mr. & Mrs." spelled exactly like that? –  Xantec Nov 3 '10 at 19:54
    
@Xantec - If it were, then why would he need to delete the word at all? Just overwrite it with however many copies of "Mr. & Mrs." you need haha! –  Shinrai Nov 3 '10 at 20:35

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
1  
I often do a version of this by pasting the text into Word, replacing spaces with tabs, and then either converting to a table within Word, or pasting back into Excel and working with it further there. The point is, let either Word or Excel deal with the separating into tokens, instead of struggling with nested Find() functions. (Especially since Find() throws an error if it doesn't find something, instead of simply returning a 0.) –  Martha Nov 3 '10 at 20:08

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.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.