22 Ways to Format Text in Google Sheets

Last Updated on February 24, 2024 by Jake Sheridan

Struggling to keep your Google Sheets tidy? In this tutorial you will learn how to hide (+ unhide) rows, columns, tabs & cells.

22 Tips for Text Formatting in Google Sheets

Trim Characters in Google Sheets

1. Remove a specific amount of characters from the left side

To remove a certain number of characters from the left of a string, we can use the RIGHT function to return a string up to a specific character starting from the last character. We’ll use the LEN function to find the character position on where to begin our string.

Use the following formula to remove a specific amount of characters from the left side of the string.

=RIGHT(A1, LEN(A1) – N)

To use the formula, simply replace A1 with the text you want to modify and replace N with the number of characters you want to remove starting from the left side or beginning of the string.

2. Remove a specific amount of characters from the right side

To remove a certain number of characters from the right of a string, we can use the LEFT function to return a string up to a specific character. We’ll use the LEN function to find the character position on where to stop returning characters.

Use the following Google Sheets formula to remove a specific amount of characters from the right side of the text string.

=LEFT(A1, LEN(A1) – N)

To use the formula above, simply replace the cell reference A1 with the text you want to modify and replace N with the number of characters you want to remove starting from the right side or end of the string.

Whitespace in Google Sheets

3. Remove leading spaces

Leading spaces refer to the space characters that are positioned before the actual text in the cell. These leading spaces can be difficult to spot and could lead to imprecise data analysis. For example, when using the VLOOKUP formula, we may not be able to find matches to a specific string if our data contains leading spaces.

We can use the TRIM function to remove leading spaces in text found in your Google Sheets document.

Here’s a simple formula that removes the leading spaces in the text found in cell A1:

=TRIM(A2)

4. Remove trailing spaces

We can also use the TRIM function to remove trailing spaces as well:

=TRIM(B1)

In the example above, we used the TRIM function to remove trailing spaces in cell B1. Using the LEN function on both the input and output, we can see that the length of our output is five characters shorter since the trailing spaces have been removed.

We can also remove leading or trailing spaces by using the built-in Data Cleanup feature in Google Sheets. Simply select your target cell or cell range and click Data > Data cleanup > Trim whitespace.

5. Replace a consecutive amount of space with 1 tab

In programming, tabs and spaces are both used to indent lines. Whether to use one or the other may depend on the conventions of the programming language or the user’s own preferences.

If you want to replace consecutive spaces with a single tab, you can use the following Google Sheets formula:

=REGEXREPLACE(A1, ” {2,}”, CHAR(9))

This function matches groups of two or more spaces using the regex pattern “ {2,}” and replaces it with the tab character (t) which we can output using the formula CHAR(9).

6. Replace 1 tab with a single or multiple spaces

We can also perform the inverse where we’ll replace a single tab with multiple spaces. In most scenarios, a tab is about the length of 8 spaces. However, the user can certainly adjust the length of the tab depending on their preferences.

=REGEXREPLACE(A1, “t”, REPT(” “, 8))

Our formula works by finding instances of the tab character in our string (t) and replacing it with eight consecutive spaces. We can automatically generate a string of eight spaces using the REPT formula. The REPT formula allows you to create a string that repeats a character or string a specified number of times.

7. Remove blank or empty lines

If your cell contains blank or empty lines, you may want to clear these lines to save space. Here’s a useful formula you can use to remove empty lines in Google Sheets:

=REGEXREPLACE(A10, “ns*n”, CHAR(10))

The REGEXREPLACE formula above uses the pattern “ns*n” which matches empty lines in a text string. The formula then replaces the matches with a single newline character instead.

8. Replace line break with space

In some cases, you may want to combine a cell with multiple lines so that all text appears in a single line. We can use the following Google Sheets formula to replace line breaks in our text with a space.

=SUBSTITUTE(A1, CHAR(10), ” “)

To find line breaks, we use the formula CHAR(10) which outputs the newline character (n). We’ll then use the SUBSTITUTE function to find newline characters in our text and replace them with a space character (” “).

9. Convert multiple spaces to single space

We can use the following REGEXREPLACE formula to convert a series of consecutive spaces into just a single space.

=REGEXREPLACE(TRIM(A1), “s+”, ” “)

The “s+” pattern allows us to match one or more consecutive spaces and replace each match with a single space. The TRIM function also allows us to remove any leading and trailing spaces from our text.

10. Convert multiple blank or empty lines with single line

Two consecutive newline characters (“nn”) creates a blank or empty line between text values. These lines can be useful to indicate two blocks of text or to separate paragraphs.

However, you may encounter text with multiple consecutive blank lines. Here’s a useful formula you can use to convert multiple consecutive empty lines with just a single line in Google Sheets:

=REGEXREPLACE(A1, “(nn)n+”, “$1”)

The REGEXREPLACE formula above checks your text for three or more consecutive new line characters and replaces the group with just two new line characters.

11. Remove all line breaks

We can use the following Google Sheets formula to remove all line breaks in a cell:

=SUBSTITUTE(A1, CHAR(10), “”)

To find line breaks, we use the formula CHAR(10) which outputs the newline character (n). We’ll then use the SUBSTITUTE function to find newline characters in our text and replace them with an empty string.

Removing Characters in Google Sheets

12. Remove punctuation marks

When cleaning your data, you may want to remove punctuation from your text fields. For example, when analyzing user-generated content, you may want to only focus on the actual words by stripping all punctuation marks such as commas, question marks, ellipses, and exclamation marks.

We can remove punctuation marks from a string in Google Sheets using the following REGEXREPLACE function:

=REGEXREPLACE(A1, “[[:punct:]]+”, “”)

The REGEXREPLACE function searches for text based on a regular expression pattern and replaces it with a certain string. In the formula above, our regular expression is “[[:punct:]]+” which matches any punctuation character. Our formula then replaces any matches with an empty string. This effectively removes the punctuation marks from the final output.

13. Strip all emojis

We can strip all emojis from a string using the following REGEXREPLACE formula:

=REGEXREPLACE(A1, “[^x20-x7E]”, “”)

The regular expression “[^x20-x7E]” matches any character that is not a printable ASCII character, which includes emoji characters. REGEXREPLACE replaces any matches with an empty string which effectively removes them from the output.

“x20” refers to the space character (the first printable character) and “x7E” refers to the tilde character (the last printable character). The hyphen indicates we are defining a specific range ( the range of printable characters. The caret symbol (^) at the start specifies that we are matching characters that are not found in the range.

14. Remove letter accents (diacritics)

Unfortunately, Google Sheets doesn’t have native functionality to remove diacritics. However, you can manually create a nested SUBSTITUTE formula for common accents.

For example, we can use the following formula to substitute a ü with u:

=SUBSTITUTE(A7,”ü”,”u”)

Since creating a formula that substitutes each possible letter accent will result in a lengthy and cumbersome formula, we recommend looking for third-party Google Sheets add-ons that may have support for this.

15. Normalize unicode letters/characters

Unicode normalization is a process that ensures that text is represented in a consistent way across different systems and applications. While Google Sheets contains a variety of text manipulation functions, it currently doesn’t have a built-in function that handles Unicode normalization.

If you do need a function to normalize text, you can use a custom Google Apps Script or a third-party add-on for Google Sheets.

16. Remove replacement character

The replacement character (�) often appears when your application encounters a character that it cannot represent. This may be due to an encoding mismatch, data corruption, or the character itself may not be supported.

Here’s how you can remove the replacement character from your text string in Google Sheets:

=SUBSTITUTE(A1, CHAR(65533), “”)

Using the SUBSTITUTE function, we can look for the replacement character using the formula CHAR(65533) and replace it with an empty string.

17. Remove non-ASCII characters

Non-ASCII characters refer to any character that is not part of the original ASCII (American Standard Code for Information Interchange) character set.

We can use the REGEXREPLACE function to remove non-ASCII characters. The regular expression “[^x00-x7F]” allows us to match any character that is not in the ASCII range.

To remove non-ASCII characters in Google Sheets, we can use the following formula:

=REGEXREPLACE(A1, “[^x00-x7F]”, “”)

Simply replace A1 with the cell reference containing your target text.

In the example above, we were able to use our formula to remove non-ASCII characters from our text such as Japanese hiragana and emoji characters.

18. Remove non-alphanumeric characters

An alphanumeric character is one that is either a letter (alphabetical character) or a number (numeric digit). When performing data cleaning, you may want to only keep these alphanumeric characters in your data.

We can use the following REGEXREPLACE function to remove non-alphanumeric characters:

=REGEXREPLACE(A1, “[^0-9A-Za-z]”, “”)

The formula uses the regular expression [^0-9A-Za-z] to match any character that is not a number (0-9) or a letter (A-Za-z). Any matching characters are then replaced with an empty string which removes the character from the output.

Case Converting in Google Sheets

19. Uppercase

In Google Sheets, you can use the UPPER function to convert all letters in a given text string to uppercase. The UPPER function contains a single argument which can be an actual text string or a reference to another cell that contains the target text.

For example, we can use the formula =UPPER(“Hello World!”) to convert all the letters to uppercase.

20. Lowercase

We can use the LOWER function to convert all letters in a given text string to uppercase. The UPPER function contains a single argument which can be an actual text string or a reference to another cell that contains the target text.

For example, we can use the formula =LOWER(“Hello World!”) to convert all the letters in our string to lowercase.

21. Capitalize each word

To capitalize each word in a string, we can use the PROPER function. This function capitalizes the first letter of each word in the specified text. A common use-case for this function is when you want to format given names or titles of specific works.

For example, we can use the formula =PROPER(“in search of lost time”) to capitalize each letter in the chosen string.

Do note that the PROPER function capitalizes the first letter of each word and converts the remaining letters to lowercase. The PROPER function also considers any character after a non-letter character as the start of a new word. These rules can be an issue with certain strings.

22. Sentence case

Another way you can transform your text is by converting it to sentence case. This will allow your strings to appear as sentences where the first word of each sentence is capitalized.

Since Google Sheets does not have a built-in method for converting text to sentence case, we will need to use a custom formula:

=JOIN(“. “,ARRAYFORMULA(

REPLACE(TRIM(SPLIT(B1,”.”)),1,1,

UPPER(LEFT(TRIM(SPLIT(B1,”.”)),1)))))

Simply replace the cell reference B1 with the target string you want to convert to sentence case.

The formula uses the SPLIT function to separate the text into multiple sentences. The UPPER, LEFT, and TRIM functions are used together to find and capitalize the leftmost character in each sentence. The REPLACE function then replaces the first letter of each sentence with the capitalized equivalent.

Summary

Hopefully this guide has given you an overview of text formatting from Google Sheets.