Find and Replace in Excel: Can one restrict to just one column?

11-7-19

This should be simple but I'm having trouble with it even so: I want to FIND a short piece of text at every one of its occurrences in one column, and REPLACE it with nothing, leaving the text in every cell which has been altered in that column otherwise intact.

It works perfectly. The trouble is, how do I restrict this action to just one column leaving cells containing the text fragment in neighboring columns unchanged? Every time I try to apply FIND and REPLACE it eliminates the text fragment EVERYWHERE that fragment occurs in the worksheet. I have tried selecting just the cells in one column but the action still seems to extend throughout the spreadsheet. I tried to cut out the column I wanted to restrict the action to with the plan of pasting it into a new worksheet as a single column, hoping to use FIND and REPLACE on it there, with no other columns to disturb, and hoping to cut out the altered column and paste in back into the original spreadsheet, having no other columns disturbed. Didn't work--never got that far-- I couldn't paste the column into a new worksheet to work on it there.

Any help would be vastly appreciated. Thanks in advance.

An easy solution to try would be to seperate that one column from all the others - Highlight the column in question that you wish to use the find and replace function on, and format the cell to fill with a colour. Then, go to use the find and replace function, click "Replace", and then click "Options". Type in the text that you want to search for, and then, to the right of that, utilise the "Format" option to select the colour of the coloumn that you just chose. This will then search the entire sheet for the text you are after, but only select the ones that also match the cell formatting you are searching for (the coloured cells). Type in what you'd like to replace it with, and use the find and replace as usual. Once done, just format the cell back to no fill colour.

I know it's too late this response for OP but it could help others. I discovered an easy way to do it.

1- Select the name of the column. Selected column H in this photo

2- Go to Home-> Find & Select -> Replace.

3- Fill the "find what" and "replace with" with what you want. example

4- Click "Find All".

5-In the lower part of the Find and Replace window it will show the table with all occurrences for that Column. How it should appear

6- Click one row of that table and do Ctrl + A, it should select the whole table.The whole table selected

7- Click Replace All. The result, you can see the column with header Medu has a cell with the content I wanted to replace but it wasn't affected because I only selected the column H with header Fedu

Unfortunately Find/Replace in MS Office just isn't this smart. You have two options:

1) You can do what you already suggested. Copy/Paste to a new workbook and perform the action there. You reported that you couldn't paste, however this absolutely should be possible and it's a perfectly valid way to do what you're after.

2) You can use VBA to programmatically do what you want. Here is a starter piece of code to do the trick:

    Private Sub ReplaceRow()
        Dim Row As Integer, Col As Integer
        Row = 1: Col = 1 'Change "Col" to equal the column you wish to search.  Change "Row" to 2 to exclude header row.
        Dim LastRow As Integer
        LastRow = Cells(Rows.Count, 1).End(xlUp).Row
        Dim ws As Worksheet
        Set ws = Me

        Dim rng As Range, cell As Range
        Set rng = Range(ws.Cells(Row, Col), ws.Cells(LastRow, Col))

        Dim strFind As String, strReplace As String
        strFind = InputBox("Enter String to Find.")
        strReplace = InputBox("Enter replacement string.")

        For Each cell In rng.Cells
            If strFind = cell Then cell.Value = strReplace
        Next cell

    End Sub 'ReplaceRow