Asked
Updated
Viewed
6.6k times

I need to make a correction to a spreadsheet document via Google Sheets. All of the cells in a column have an extra character that we need to remove.

Is there a way to remove a character or string of characters in all cells of a spreadsheet column? We could do this manually, but it would be tedious and I'd rather find a smarter or more automatic way of performing this deletion across all cells in the column.

add a comment
1

1 Answer

  • Votes
  • Oldest
  • Latest
Answered
Updated

I think you can do this with find and replace in Google Sheets. This help article may be useful. Depending on what you need to have removed it could be a simple search and replace or a more complex search and replace using patterns such as regular expressions. To do a search and replace in Google Sheets:

  1. Click the Column Header to select the entire column you want to perform the replacement on
  2. Click on Edit, then on Find & Replace (or alternatively simply use hotkey Ctrl-H)
  3. Next to Find enter the character or string of characters to match
  4. Next to Replace With use an empty string if you simply want to remove
  5. You can Match Case or use Regular Expressions for more advanced matching
  6. To replace every instance click on Replace All

You will notice since you already selected the entire column before you went to the Find & Replace tool that it automatically prefilled the search box with a specific range being just the column you selected:

Google Sheets Find and Replace Modal

With all of the above said, if you are simply needing to remove a single character, for example, let's say the letter z, then you will need to use regular expressions otherwise, all instances of the letter z would be replaced. For example, let's say that for some reason in each cell in the column you had a z at the very end of each cell. You could then do the following for Find to only match the letter z at the end:

z$

The dollar sign with regular expressions indicates that it must be at the end. The find and replace window should look like this:

Google Sheets Find and Replace with regular expressions

Make sure yo select the box for Search using regular expressions. Regular expressions can get much more complex than this, but they give you complete control on what is matched if you need something very specific that you want to be removed or perhaps replaced with alternate text.

add a comment
0