3

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.

7
  • "Didn't work--never got that far-- I couldn't paste the column into a new worksheet" - what do you mean by that? Nov 7 '19 at 21:51
  • "I want to FIND a short piece of text at every one of its occurrences in one column" - which version of Excel do you use? I use Excel 2013 and whenever more than one cell is selected Excel performs find and replace only in the selection, and not the entire worksheet. Nov 7 '19 at 21:52
  • "leaving the text in every cell which has been altered in that column otherwise intact." I've read that part five times, but still struggle to understand what do you need. Nov 7 '19 at 21:55
  • 1
    ??? If there is a selection on the worksheet (row, column, range, multi-range - any except single-cell) then Search&Replace is restricted this selection only. So select the column in question and use Search&Replace...
    – Akina
    Nov 8 '19 at 5:04
  • 1
    Only select the column you need to replace and Ctrl+F.
    – Lee
    Nov 8 '19 at 9:28
5

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.

1
  • Might not work for all cases but I gave a +1 because the format option is a wonderful workaround to achieve this. I just formatted the column with something unique and than I used the "find format" option and it worked like a charm, once done I removed the formatting from that column again.
    – rodedo
    May 28 at 16:04
2

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

1

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
3
  • 1
    I would Dim the variables as Long: Excel can handle more rows than Integer allows for Nov 8 '19 at 15:27
  • 11-11-19 I very much appreciate this sophisticated reply but unfortunately it's over my head and likely to remain so. But thanks very much.
    – George
    Nov 11 '19 at 15:20
  • Quite right Cybernetic. I tend to forget to do that since using such large values is rare for me. I generally get the overflow error and then correct. Bad of me. :) Nov 15 '19 at 15:03
0

A workaround is to make a new file, paste the selected cells there and then do the find/replace in that file. Paste these cells back in the original file. So, step by step: -Copy the selected cells -Create a new file -Paste the cells -Do the find/replace -Copy the corrected cells -Paste them in the original file

Very strange that Excel and also Numbers on the Mac, can't do a search in selected cells only.

0

I needed to change M or F to numbers in a column marked "SEx at Birth". I highlighted the column and formattted it. I opened the Find & Select dialog box. Based on previous guidance here, I opened the Format dropdown and chose "Choose format from cell" but I am not sure that is necessary. However, there is an option to choose to search By Columns and to "Match entire cell contents" and to "Match case" That solved my problem.

-1

If you want to restrict Find & Replace in particular column then, you need to click the column's alphabet to select entire column, Find and Replace will operate within that column only.

Note, for multiple column selection use Ctrl + select columns (as shown above ).

enter image description here

  • As soon you perform Find or Find & Replace, Excel will find John in cell O3

enter image description here

  • After Find & Replace:

Note, use Replace instead of Replace All.

enter image description here


  • For multiple column selection:

enter image description here


enter image description here

  • After Find & Replace:

enter image description here

Note, use Replace instead of Replace All.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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