You can use a Regex formula or the built-in menu option Data > Data clean-up > Trim whitespace to remove whitespaces at the beginning of a newline in Google Sheets.
The TRIM function won’t return the desired result for this task.
That’s because the purpose of TRIM is to remove spaces (ASCII 32) from text in a cell—whether they are repeated, leading, or trailing spaces. However, it doesn’t give you a way to target newlines.
TRIM Spaces and the Issue with Newline Characters
Note: Whitespace refers to any character that creates blank areas or text breaks, such as spaces, tabs, carriage returns, and newline characters. However, the TRIM function in Google Sheets only removes ASCII 32 (the standard space). It does not remove tabs (char(9)), carriage returns (char(13)), or newlines (char(10)).
For example, I have the below texts in A1:C1.
- A1 – Cat
- B1 – and
- C1 – Dog
=A1&CHAR(9)&B1&CHAR(9)&C1The above formula will return the string "Cat and Dog", similar to the one below:
=A1&CHAR(32)&B1&CHAR(32)&C1Both CHAR(9) (tab) and CHAR(32) (space) are types of whitespace and visually render as space between the words.
Back to our topic—when using a formula to remove whitespaces at the beginning of a newline, we need to match a specific pattern: a line break followed by one or more spaces.
Using a regular expression (RE2), we can search for this pattern and replace it with just a line break.
Examples of Removing Whitespaces at the Beginning of a Newline in Google Sheets
As mentioned earlier, there are two methods:
- Using the menu command (Data Cleanup)
- Using a formula with REGEXREPLACE
Let’s go through them one by one.
Using Data Cleanup
Steps:
- Select A2:A11.
- Go to the Data menu > Data Clean-up > Trim whitespace.
That’s it! This is the quickest way to remove whitespaces at the beginning of every newline in Google Sheets.
Points to Note:
- This action also removes all leading, trailing, and repeated spaces.
- There’s a difference between newline types:
- In cell A10 – the newline is the line feed character (ASCII 10). This happens because we manually entered multiple lines in the same cell (using Alt+Enter on Windows or Option+Enter on Mac).
- In cell A11 – the newline is the carriage return character (ASCII 13). This occurs when we copy multiple rows and paste them into a single cell.
As a result, cell A11 may lose formatting after cleanup. To fix it, simply press Enter in that cell after trimming.
Formula to Remove Whitespaces at the Beginning of a Newline
If you prefer a formula-based approach, try this array formula in B2 (it will expand down):
=ArrayFormula(REGEXREPLACE(TRIM(A2:A11), "\n\s+", CHAR(10)))- TRIM removes extra spaces.
- REGEXREPLACE handles newlines and spaces:
\nmatches a newline character.\smatches any whitespace character.+ensures one or more spaces are matched and replaced.
If you’re working with a single cell (like A2), you can remove ArrayFormula.
Final Thoughts on Removing Whitespaces in Google Sheets
Removing whitespaces at the beginning of a newline in Google Sheets may seem tricky at first, but with the right method it becomes straightforward. If you prefer a quick fix, the Data Cleanup > Trim whitespace option works well. For more control and flexibility, the REGEXREPLACE formula is the better choice.
