# Removing manual line breaks in Excel for Macs
**URL:** https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112
**Category:** Factual Questions
**Created:** [August 1, 2024, 2:07pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112 "2024-08-01T14:07:05Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)
#### Post date: [August 1, 2024, 2:07pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/1 "2024-08-01T14:07:05Z")
Some of the data I receive has non-display characters that cause problems when converting the file to text. (They break the record.) On a PC I can search-and-replace ALT+010 with pipes. I can’t make an ALT+010 on my mac. Someone here (a Doper from Tel Aviv – I’ve forgotten his name) gave me a work-around: Copy the file into Word, choose Editing\>Replace\>Special\>Manual line break and put in a pipe. This not only gets rid of the ALT+010 characters, but also other non-display characters that cause the problem, and whose codes I don’t know. It works great. After fixing the breaks, I copy the Word document back into the Excel spreadsheet.
But of course there’s a problem. Some larger files either take forever to paste into Word, or they take so long that I assume the Paste isn’t working. This is the case for a file I have right now that has 29,000 records.
I’ve emailed the file to myself, and I’m trying to fix it entirely on the Mac; as opposed to my Mac connected to my office PC. I searched for a solution and found [this one](https://answers.microsoft.com/en-us/msoffice/forum/all/how-can-i-findreplace-line-breaks-in-mac-2008/fc983c79-7a62-4bdc-8dcc-0b1da2d80bb1). It says to use =SUBSTITUTE. Following the instructions, I have put =SUBSTITUTE(I24,CHAR(13),“|”) into an adjacent cell, and I inserted a manual line break between two words (option+return). The option+return put in a manual line break. The SUBSTITUTE formula put the words on a single line (which is good), but did not insert a pipe between the words. (I’ve also tried using a space instead of a pipe.)
Why does the SUBSTITUTE command not put a pipe in place of a manual line break?
If I can get the substitute formula to work properly, I’ll have to do it in close to a dozen columns; but at least it would be quicker than waiting for the records to be pasted into Word.
---
### Author:  [@Stranger\_On\_A\_Train](https://boards.straightdope.com/u/Stranger_On_A_Train)
#### Post date: [August 1, 2024, 2:23pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/2 "2024-08-01T14:23:43Z")
> [@Johnny\_L.A](#):
>
> I’ve emailed the file to myself, and I’m trying to fix it entirely on the Mac; as opposed to my Mac connected to my office PC. I searched for a solution and found [this one](https://answers.microsoft.com/en-us/msoffice/forum/all/how-can-i-findreplace-line-breaks-in-mac-2008/fc983c79-7a62-4bdc-8dcc-0b1da2d80bb1). It says to use =SUBSTITUTE. Following the instructions, I have put =SUBSTITUTE(I24,CHAR(13),“|”) into an adjacent cell, and I inserted a manual line break between two words (option+return). The option+return put in a manual line break. The SUBSTITUTE formula put the words on a single line (which is good), but did not insert a pipe between the words. (I’ve also tried using a space instead of a pipe.)
Reading this makes my head hurt like a five martini hangover.
Learn to process text files in Python or Perl. It will save you so much time and frustration over trying to make Excel do things that it was never designed to do, or copying files over to Word to just to eliminate carriage returns.
Stranger
---
### Author:  [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)
#### Post date: [August 1, 2024, 2:29pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/3 "2024-08-01T14:29:30Z")
> [@Stranger\_On\_A\_Train](#):
>
> Learn to process text files in Python or Perl.
I would have to take an in-person class for that. It’s not something I’d be able to learn from a book.
As for the current file: I could wait until Tuesday when I’m in the office and change ALT+010 to pipes, but this file also has the other manual line breaks I don’t know the code for.
The file was _finally_ copied into Word for Macs. I can do a Find using the Search in Document box and putting in ^l (lowercase L), and I can find the breaks that way. ~~But I don’t see in Word for Macs how to do a Find & Replace. I can’t find an icon for that.~~ I found Replace by right-clicking the Find box.
Now I need to copy-and-paste the Word document back into Excel. That might take an hour.
---
### Author:  [@Stranger\_On\_A\_Train](https://boards.straightdope.com/u/Stranger_On_A_Train)
#### Post date: [August 1, 2024, 2:39pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/4 "2024-08-01T14:39:00Z")
> [@Johnny\_L.A](#):
>
> I would have to take an in-person class for that. It’s not something I’d be able to learn from a book.
It would literally take about ten minutes to learn how to import the necessary modules, open a file for read/write access, and do a text replacement. You don’t even need to crack a book, actually; there are plenty of examples on [stackoverflow.com](http://stackoverflow.com) (and doubtless other sites) such as [this one](https://stackoverflow.com/questions/4128144/replace-string-within-file-contents#4128192) which you can just crib and modify as necessary.
Stranger
---
### Author:  [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)
#### Post date: [August 1, 2024, 2:46pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/5 "2024-08-01T14:46:49Z")
> [@Stranger\_On\_A\_Train](#):
>
> such as [this one](https://stackoverflow.com/questions/4128144/replace-string-within-file-contents#4128192) which you can just crib and modify as necessary.
I use Easytrieve, which is an ancient sequential language. I have no experience with OO languages. But anyway, your link points to this:
```auto
with open("Stud.txt", "rt") as fin:
with open("out.txt", "wt") as fout:
for line in fin:
fout.write(line.replace('A', 'Orange'))
```
I assume that ‘Stud.text’ is the file you want to modify. In my case it would be ‘ar\_file.xlsx’. I assume that ‘out.txt’ is the output file. In my case it would be ‘ar\_file\_fixed’. And it appears that the code replaces all instances of capital A with the word ‘Orange’.
But I haven’t a clue how to run it. And it seems four lines aren’t enough. It looks like it’s only part of a routine.
---
### Author:  [@beowulff](https://boards.straightdope.com/u/beowulff)
#### Post date: [August 1, 2024, 2:52pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/6 "2024-08-01T14:52:04Z")
Are these plain text files?
If so, get the free version of BBEdit, and just search and replace for the character you want.
You can “show invisibles” and copy the character you want to search for, if you don’t know how to generate it with the keyboard.
---
### Author:  [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)
#### Post date: [August 1, 2024, 2:52pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/7 "2024-08-01T14:52:49Z")
No, they’re Excel files. I need to get rid of the NDCs so that I can turn them into text files.
---
### Author:  [@beowulff](https://boards.straightdope.com/u/beowulff)
#### Post date: [August 1, 2024, 2:53pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/8 "2024-08-01T14:53:35Z")
You might still try BBEdit.
---
### Author:  [@Dewey\_Finn](https://boards.straightdope.com/u/Dewey_Finn)
#### Post date: [August 1, 2024, 2:58pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/9 "2024-08-01T14:58:37Z")
> [@Johnny\_L.A](#):
>
> Now I need to copy-and-paste the Word document back into Excel. That might take an hour.
Why would it take more than a moment?
---
### Author:  [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)
#### Post date: [August 1, 2024, 2:58pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/10 "2024-08-01T14:58:51Z")
I hate to be dense, but I don’t know what that is.
In any case, the file finally copied into Word and I was able to change the NDCs to pipes. I’ve sent the file back to my office PC and am just waiting for the virus scan to finish.
The Aging file is much smaller, and I was able to copy it into word in a couple/few minutes for editing.
> [@Dewey\_Finn](#):
>
> Why would it take more than a moment?
I haven’t a clue. I was hoping it would be quicker on my Mac than it is on the PC, but this is not the case.
---
### Author:  [@Dewey\_Finn](https://boards.straightdope.com/u/Dewey_Finn)
#### Post date: [August 1, 2024, 3:01pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/11 "2024-08-01T15:01:13Z")
CTRL-A, CTRL-C, CTRL-V or the Macintosh equivalent.
---
### Author:  [@Pork\_Rind](https://boards.straightdope.com/u/Pork_Rind)
#### Post date: [August 1, 2024, 3:01pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/12 "2024-08-01T15:01:48Z")
I just gave it a try myself and found that I needed to use char(10) to see the pipe character inserted. Char(13) removed the break but did not add the pipe.
`=SUBSTITUTE(E6,CHAR(10),"|")`
---
### Author:  [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)
#### Post date: [August 1, 2024, 3:02pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/13 "2024-08-01T15:02:06Z")
Yes, I know that. That’s what I do. It still takes forever to copy a large Excel file into Word.
---
### Author:  [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)
#### Post date: [August 1, 2024, 3:07pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/14 "2024-08-01T15:07:15Z")
> [@Pork\_Rind](#):
>
> I just gave it a try myself and found that I needed to use char(10) to see the pipe character inserted.
That worked! Thank you.
---
### Author:  [@Shoeless](https://boards.straightdope.com/u/Shoeless)
#### Post date: [August 1, 2024, 3:18pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/15 "2024-08-01T15:18:04Z")
I guess I’m not understanding why you have to copy and paste into Word to clean up the line breaks, then paste back into Excel so you can export to text. It sounds like what you need is a plain text editor that can display non printable characters. @beowulff recommended BBEdit. I use Notepad++ but I’m not sure there is a Mac version. But I can’t imagine why you can’t just export to text or csv from Excel and then do your line break cleanup directly in the text file with a text editor using a simple find/replace.
---
### Author:  [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)
#### Post date: [August 1, 2024, 3:25pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/16 "2024-08-01T15:25:34Z")
> [@Shoeless](#):
>
> I guess I’m not understanding why you have to copy and paste into Word to clean up the line breaks, then paste back into Excel so you can export to text.
I telecommute most of the time, and I use a Mac. I connect directly to my office PC to work. A couple/few files I receive have non-display characters. On the PC, I can find ALT+010 and replace with pipes. This is impossible on a Mac. Also, one or two files have non-display characters I don’t know the ALT codes for. So the Excel file needs to be copied into Word so that I can replace manual line breaks with pipes. This takes care of all of the NDCs, whether they are ALT+010 or the other ones. Once replaced, I can Paste as text back into the Excel file.
Why not just save the Excel file as Text? Because when I started doing this 20 years ago, there were two ways of saving Excel files as text. One way truncated the records (which are hundreds of characters long), and the other way split the records and put the ends at the bottom of the file. Easytrieve requires fixed-position text files. Fields _must_ be in the proper position and be the proper length. So I save the Excel files as .csv files and import them into Access, then export the files as fixed-position text.
When there is a manual line break, Access breaks the record upon exporting it.
---
### Author:  [@Stranger\_On\_A\_Train](https://boards.straightdope.com/u/Stranger_On_A_Train)
#### Post date: [August 1, 2024, 3:50pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/17 "2024-08-01T15:50:08Z")
> [@Johnny\_L.A](#):
>
> I assume that ‘Stud.text’ is the file you want to modify. In my case it would be ‘ar\_file.xlsx’. I assume that ‘out.txt’ is the output file. In my case it would be ‘ar\_file\_fixed’. And it appears that the code replaces all instances of capital A with the word ‘Orange’.
>
> But I haven’t a clue how to run it. And it seems four lines aren’t enough. It looks like it’s only part of a routine.
First, you need to save the file as a CSV (comma separated file) so that it is plaintext instead of Excel format. (There are libraries for working with various versions of Excel from Python but not really necessary in your case since you want to export to a text file anyway.)
The reason that copy and paste from Excel to Word or reverse takes so long is because you are copying the entire text and formatting of the document into the clipboard, which it was never intended to facilitate. This is a terrible, time-wasting ‘hack’ that, as you note, doesn’t work well or at all as filesize increases.
The `with open(filename, read/write) as filehandle:` just opens a text file for reading or writing as . The `for line in filehandle:` iterates through the lines of the file. The `filehandle.write(line.replace())` command does the actual text replacement. In the case of “non-display characters” (which is what I interpret what you mean by “NDC”), you can just have it [trim off all of the non-ASCII characters](https://pythonguides.com/remove-non-ascii-characters-python/), or use a _regular expression_ (regex), or any number of other solutions. All you need to do to run this on your Mac (which has Python installed in the system) is copy and edit this into a file (_scriptname.py_) in the directory where your CSV file is, open up a Terminal in the folder where your files are, type `python`, and then `run scriptname.py`. Those four lines are literally all you need to do this task.
For all the time you spend fucking around with Easytrieve, and Excel and Word, you could just spend a few hours and learn some very basic functionality in Python or Perl, write some generic functions to take inputs like filenames and text formats, and essentially automate this entire process without ever again firing up Easytrieve or Excel. You don’t need to know anything about object-oriented programming, or any obscure libraries, or even most of the functionality of a language like Python to do this simple thing. Most community colleges have extension classes which include basic programming if you have to have an in-person learning experience, although for what you are trying to do you could pay some junior high school nerd twenty bucks, or just go on stackexchange and ask people to solve your problem for you. Any of these options would be less painful, both for you and the posters here who keep giving you guidance on how to create a functional solution instead of weird, inefficient hacks to add to what already seems to be a convoluted process just to process and format text data.
Stranger
---
### Author:  [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)
#### Post date: [August 1, 2024, 4:00pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/18 "2024-08-01T16:00:14Z")
> [@Stranger\_On\_A\_Train](#):
>
> Any of these options would be less painful, both for you and the posters here who keep giving you guidance on how to create a functional solution instead of weird, inefficient hacks to add to what already seems to be a convoluted process just to process and format text data.
I agree that there is a better solutions. Unfortunately they came about long after ‘my time’. I have to use the tools I have. If I had one ‘routine’ or whatever that someone sets up, I could _probably_ adapt it. But I’d need a template and the tools to run it (and we’re not allowed to install anything on our computers).
Sorry I’m such a dinosaur.
---
### Author:  [@DPRK](https://boards.straightdope.com/u/DPRK)
#### Post date: [August 1, 2024, 10:32pm UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/19 "2024-08-01T22:32:53Z")
Seconding (thirding?) that if you are not using a script and want to edit a text file manually, you need a text editor (not Word). Literally any one will do (except possibly for a shitty one without proper search-and-replace). Are there any text editors installed on your computer (since you say you are not permitted to install additional software)? I am positive there is at least _something_ (perhaps TextEdit and/or vi/vim?)
Excel files are _not_ plain text files, so you will need to work with CSV as explained in Stranger\_On\_A\_Train’s message.
You can call yourself a “dinosaur” if you want, but Word does not enter into it; as you said yourself in the first message, it chokes, so you cannot really use it.
---
### Author:  [@AHunter3](https://boards.straightdope.com/u/AHunter3)
#### Post date: [August 3, 2024, 4:46am UTC](https://boards.straightdope.com/t/removing-manual-line-breaks-in-excel-for-macs/1005112/20 "2024-08-03T04:46:51Z")
Download BBEdit. Save Excel file as .csv format. Open in BBEdit. It is very good at revealing all the various line break codes (there are more than one of them) and letting you mass-substitute to nuke the ones you don’t want.
I do it all the time to convert .csv files that use carriage return to .csv files that use line feeds because Microsoft Word for Windows’ mailmerge function is ridiculously pickly and only accepts line feed line breaks.