I want to replace a comma with a carriage return in LibreCalc. Is it possible to use Find & Replace? I can’t figure out how to enter the carriage return in the Replace box.
Hi
If the text is in column A you can use in column B the formula :
=SUBSTITUTE(A1;", ";UNICHAR(10))
Note: I am replacing a comma followed by a space; if there is no space in your case, you should not add this space.
Then you just have to copy column B and do a paste special Values & Formats (without the formula) on column A
Regards
Thanks! This method worked perfectly for me because I was putting the data into new cells anyway.
You need to use Regular Expressions. Look at this list List of Regular Expressions
Select the text if only a selection is needed (optional).
Open Find & Replace (Ctrl+H).
- In Find enter the comma
, - In Replace enter
\n - Under Other options
- Tick box Current Selection only (optional)
- Tick Box Regular expressions (required)
Click Replace All or variations of Find Next and Replace.
\n doesn’t work in Calc.
Sorry I misread. I don’t think you can do that in calc
It is simple by macro, but text in cells will stay in one line and I don’t know why 
Libre 7.3.2.2 Win10x64
replCR.ods (32.7 kB)
- U+000D (CR) can be inserted in Calc, but isn’t shown in the view.
- After a PasteSpecial, values only, it is replaced with U+000A (LF).
- Also the persistent U+000A can be inserted by user code using a ReplaceDescriptor.
- It will not trigger adaption under OptimalRowheight, however.
- Getting the DataArray of a respective range, and setting it back will trigger the adaption.
- The dialog for F&R in Calc always passes
\nas a literal.
That’s “research results” under LibO V 7.3.1.3.
See attached:
disask76389justForConnoisseursAndFun.ods (26.3 KB)
1 Like