Sometimes the text in Google Sheets isn’t exactly how you want it while working with data. You may have extra symbols, letters, or numbers at the start, middle, or end of a cell. Google Sheets offers some simple tools for organizing data. Simple formulas can remove a single character, a few characters from the middle, or specific symbols like $ or %. In this article, I will describe how to quickly and simply remove characters from your data.
What Does Removing Characters Mean in Google Sheets?
Removing characters means deleting symbols, random integers, or additional spaces in the text that are not needed in a cell. It facilitates reading, organizing, and analyzing your data. It will be helpful when you want to:
- Remove price signs such as $45.00 → 45.00.
- Remove specific letters from product codes to make them organized.
- Remove any additional punctuation or spaces..
- Remove digits from a text string.
Removing Special Characters in Google Sheets
Special characters like @, #, %, $, or ! may appear in your sheet while working with imported or disorganized data. These characters may make cleaning up or evaluating your data more difficult. You can remove them in a few simple steps by using the REGEXREPLACE function. What you have to do is:
➤ Click on cell C2 and write this formula:
➤ Press Enter. Now you will see the result:
➤ Drag the formula down to apply it to the rest of the column.
Removing Commas in Google Sheets
Commas are frequently used in text or numbers (such as 1,000), but sometimes they must be removed, particularly when organizing clean lists or getting data ready for calculations. The SUBSTITUTE function is the simplest method for the elimination of commas in Google Sheets. What you have to do is:
➤ Click on cell C2 and write this formula:
➤ Press Enter. Now you will see the result:
➤ Drag the formula down to apply it to the rest of the column.
Removing Characters from String in Google Sheets
Sometimes you want to eliminate unwanted characters from a string, such as letters, numbers, symbols, or spaces, from your data. A few basic features in Google Sheets make it easy to clean up your data. Suppose your Google Sheets contains dash characters. To remove dashes, you have to follow these steps:
➤ Click on cell C2 and write this formula:
➤ Press Enter. Now you will see the result:
➤Drag the formula down to apply it to the rest of the column.
Removing the First Character in Google Sheets
If you want to remove only the first character from each cell in a list of text or numbers on your sheet, what you have to do is:
➤ Click on cell C2 and write this formula:
➤ Press Enter. Now you will see the result:
➤ Drag the formula down to apply it to the rest of the column.
Removing the Last Character in Google Sheets
If you have a list or data that contains unnecessary characters at the end of the text, like extra space, comma, dash or anything else, and you want to remove the last characters from the cell. What you have to do is:
➤ Click on cell C2 and write this formula:
➤ Press Enter. Now you will see the result:
➤ Drag the formula down to apply it to the rest of the column.
Removing the First N Characters in Google Sheets
When you want to remove the first few letters (such as the first two, three, or more) instead of removing the first one. You can do it with a simple formula in Google Sheets. Suppose you want to remove the first 3 characters in Google Sheets. What you have to do is:
➤ Click on cell C2 and write this formula:
➤ Press Enter. Now you will see the result:
➤ Drag the formula down to apply it to the rest of the column.
Frequently Asked Questions:
What will happen if I try to remove more characters than the text allows?
The formula will return a blank cell if the text in Google Sheets is less than the character count that has to be deleted.
When deleting characters, should I worry about spaces?
Yes! Characters are considered as spaces. If it is a space, it will be eliminated just like any other character.
Is it possible to eliminate characters from several cells simultaneously?
Yes! Spaces are considered as characters. It will be removed like any other character if it is a space.
Concluding Words
Removing characters is not as difficult as it seems. A simple formula can remove the first letter, the last symbol, or a few letters in between. It’s easy to clean up your text once you know which function to use, such as RIGHT, LEFT, or SUBSTITUTE. By using these functions, you can keep your spreadsheet clean and readable.