We can use a Regex formula or the Data > Data clean-up > Trim whitespace to remove whitespace at the beginning of a newline in Google Sheets.

The TRIM function won’t return the desired result when you use it for the said purpose.

The purpose of TRIM is to remove spaces (ASCII 32) from a text in a cell. It can be repeated spaces, leading or trailing spaces.

There is no way to specify a Newline in it.

Note:- Please note that whitespace is any character that represents (or renders as) a space.

For example, I have the below texts in A1:C1.

A1 – Cat
B1 – and
C1 – Dog

=A1&char(9)&B1&char(9)&C1

The above formula will return the string “Cat and Dog” similar to the below one.

=A1&char(32)&B1&char(32)&C1

Both char(9) and char(32) render a space.

You May Like: Learn Google Sheets CHAR Function and How to Use It in QUERY Formula.

Let’s back to our topic.

If we use a formula to remove whitespaces at the beginning of a Newline, we may require to match a pattern, i.e., a line break and whitespace(s).

What we will do here in the formula is match the required pattern [a line break and whitespace(s)] in a search operation using an RE2 regular expression and substitute it with just a line break.

Examples to Removing Whitespaces at the Beginning of a Newline in Google Sheets

As I’ve mentioned at the beginning, there are two methods. Let’s go to them one by one.

I am starting with the menu command.

Using Data Cleanup

Steps:

1. Select A2:A11.
2. Go to the Data menu and select Data Clean-up > Trim whitespace. That’s it!

The above is the quickest way to remove whitespaces at the beginning of every Newline in Google Sheets.

Points to be Noted:-

The above action will also remove all the leading, trailing, and repeated whitespace(s).

There is one more very important point.

Please check the values in cells A10 and A11. Visually, both are the same.

But in cell A11, I have copied the values from the range A2:A8 and pasted them in it, whereas, in cell A10, I have entered them manually.

As a result, in cell A11, the Newline is a carriage return (ASCII 13), whereas, in cell A10, it is the Newline character (ASCII 10).

You can test it using =code(mid(A10,14,1)) and =code(mid(A11,14,1)) formulas.

So, when you apply the above menu command, cell A11 may lose the formatting.

In that case, once you have applied the data cleanup, go to that cell (here cell A11) and hit the enter key.

Formula to Remove Whitespaces at the Beginning of a Newline Character

Some of you may prefer a formula-based approach.

If you are one among them, try the below array formula in cell B2. It will expand down.

=ArrayFormula(REGEXREPLACE(trim(A2:A11),"\n\s+",char(10)))

What is the role of the TRIM here?

It removes the extra spaces.

What is the role of the REGEXREPLACE in the formula above?

Here you go!

\n – It matches a newline character.

\s – To match any whitespace character.

+ – It matches the previous token (here, whitespace) between one and unlimited times.

You can remove the ArrayFormula function if the reference is not an array, for example, A2 instead of A2:A11.

That’s all about how to remove whitespaces at the beginning of a Newline in Google Sheets.

Thanks for the stay. Enjoy!

Resources

• How to Move New Lines in a Cell to Columns in Google Sheets.
• Start New Lines Within a Cell in Google Sheets – Desktop and Mobile.
• Regex to Replace the Last Occurrence of a Character in Google Sheets.
• RegexReplace to Wrap Numbers in Brackets and Its Use in Query.
• Split a Text after Every Nth Word in Google Sheets (Using Regex and Split).
• Regex to Get All Words after Nth Word in a Sentence in Google Sheets.
• How to Replace Commas within or outside Brackets in Google Sheets – Regex.