0

I am editing a Google Sheet with multiple peers. I need to do a Find-Replace on just my rows, say, rows 1:38 and 197:222. (Not permitted to sort the data.) I see many examples on the web of doing this over multiple sheets, ex:

 Sheet1!K37:K68;Sheet2!K37:K68

I figured for my data the following would work:

 Sheet1!K37:K38;Sheet1!K197:K208

No, "Invalid Range". Next tried:

 Sheet1!K37:K38;K197:K208

No joy again. I went back to the web and did Google searches and SE searches but could not find an example that matches my requirements. This should be really easy. What am I missing?

2
  • If you give a sample sheet and a more specific example there is literally tons of ways to resolve this issue. As there is no "simple" way of doing what you are asking. For example you can simply copy the sheet, now you have permission to sort the data, make a row number column prior to sorting then you can sort it back to it's original order before you paste it back in. – CodeCamper Apr 6 '20 at 3:19
  • Sample sheet linked below. I stripped down the data to just one column that is the area of interest. You can see that this telephone number column has a variety of different formats based on the web sites from which we copy-pasted. Keeping in mind that I can't re-sort, I want to use "Find-Replace" with RegExs to consistify the format any entries in the following rows: 2-38, 197-222, 314-386, 524-596, 688-754, 974-892. drive.google.com/file/d/1Mtsb2sd383O1I0PJM87DGyp8o95NQKGy/… – Dave Apr 9 '20 at 22:28
0

If you give a sample sheet and more specific examples I can accommodate this answer towards a more specific example.

You have permission to sort...

Step 1) Copy the sheet

Step 2) Create an extra column with the original row order

Step 3) Sort the sheet by your entries

Step 4) Find and replace your changes which are now congruous

Step 5) Sort back with the column you made in step 2

Step 6) Copy all your changes back into the original sheet in step 1

Of course getting permission to resort and having the original row order column in the original sheet is a better/common sense solution.

1
  • I see that your work-around will work. I was hoping there was a way to do it using the "specific range" field in Google Sheets "Find - Replace" pop-up. It would be so very strange to me that I can select ranges on different sheets but not ranges on the same sheet. – Dave Apr 9 '20 at 22:09

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.