As it seems there are suggestions for how to replace a character or a string with newline in Excel for Mac, like this one. But trying to reverse the process does not work; e.g., to type CTRL+J or ALT+0010 etc. Any advice on how to replace newlines in cells with, for example, space?
Mac – How to replace newline in Excel for Mac
macms office
Related Solutions
Found it! It's a setting under Preferences > Edit:
Extend data range formats and formulas
Misread it as "date ranges" on the first trawl through preferences. Turning it off stops this happening.
The comment about formulas seems to only mean that it doesn't fill empty cells with formulas when it thinks it's seen a pattern. After turning it off, it still does other automatic format adjustment e.g. incrementing formulas copied across a range:
try char(202) instead of char(160). That seems to be the code in Excel Mac
The reason is that your Excel Mac is working in the old MacRoman character set, where 160 is a dagger (U+2020) and 202 is a no break space (U+00A0)
Related Question
- Excel for Mac – How to Include a Tab in a Cell: Mac Equivalent of CHAR(9)
- MacOS – Why can’t I change the normal template for microsoft word 2016 for mac
- Automate Excel to Convert .xls to .csv with AppleScript
- MacOS – Excel API data query – populates into a single column
- Mac equivalent of ALT+010 for Windows (Replace Carriage Return/Line Breaks)
- Mac Excel – How to Replace with Newline
Best Answer
Solution for some cases could be:
Copy cell(s) (⌘ + C), paste special (⌃ + ⌘ + V), select 'Values', click 'OK'
Caveats: there must be space at the end of line, otherwise there will be no separation between lines (it removes newline but doesn't replace); it doesn't work in-cell (source and destination cells must be different)