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?
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?
Share Improve this question edited Jan 30 at 11:00 jonrsharpe 122k30 gold badges267 silver badges474 bronze badges asked Jan 30 at 10:53 AnneAnne 191 silver badge1 bronze badge2 Answers
Reset to default 2You 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))))
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().