CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Anne
Anne

Reputation: 19

Remove space after line before new line

SCREENSHOT

I need to remove the space before the new line, within the cell. I know TRIM should work for this, but I believe it only deletes the very last space of the string.

So I probably need to replace space+CHAR(10) into CHAR(10) only. But I'm not sure which formula is best for this. Any ideas?

Upvotes: 0

Views: 65

Answers (2)

doubleunary
doubleunary

Reputation: 19155

Use split() to break text into parts at newlines, trim() to remove spaces at the start and end of lines and to replace repeated spaces within lines, and finally join() to put the parts back together, like this:

=join(char(10), sort(trim(split(A1, char(10)))))

The sort() function is only used to array enable the trim() and doesn't actually sort anything in this context.

See split(), trim() and join().

Upvotes: 0

Alma_Matters
Alma_Matters

Reputation: 903

You can try these Formulas using REGEXREPLACE:

=REGEXREPLACE(A1, "\s+\n", CHAR(10))

Or

=REGEXREPLACE(A1, "\s+($|\n)", "$1")

Also, you can try using REDUCE:

=REDUCE(,SPLIT(A1, CHAR(10)), LAMBDA(a,c, JOIN(REGEXREPLACE(c,"\s[^ ]*$",""),a,CHAR(10))))

Upvotes: 2

Related Questions