Ask a new question

How can I Find/Replace line breaks in Mac 2008 Excel

I'm using Excel within Office 2008 for Mac. In a large spreadsheet, I need to replace all line breaks within a cell with a space. I understand that to CREATE a line break, I would use Ctrl+Alt+Enter, but the Find box does not accept that. It also does not accept a pasted line break. I've tried everything I can think of. According to other forums, Windows versions of Excel can enter an ASCII value for a line break by holding down the ALT key while typing the ASCII code for a line break (0010). But this apparently does not work in Mac. Please help! Thanks.
Answer
Answer
The simplest way (though with a large spreadsheet, it may still not be simple) is to insert a blank column next to cells containing line breaks. In that column (say, column B), enter

B1: =SUBSTITUTE(A1,CHAR(13),"")

or

B1: =SUBSTITUTE(A1,CHAR(13)," ")

depending on whether you want to keep a space between the end of one line and the beginning of another.

Copy down as far as required. Select the column. Copy it. Select the original column. Choose Edit/Paste Special, selecting the Values radio button.

Delete the column you inserted.

25 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

Hello 8XyZxYz8,

 

In addition to the great suggestion above, this workaround may help:

 

1. Select the entire area that you want to edit within your Excel spreadsheet and select to Copy

2. Paste this data into an empty Word document.

3. Select Edit then Find….

4. Click the triangle in the bottom left corner, then select the bar in the bottom right corner which says Special and select “Manual line break”

5. The following icon should appear in the search bar: ^l

6. Next, select the Replace bar in the top of the Window

7. In the “Replace with” box, enter a single space

8. Select Replace All

9. Now paste your content back into your Excel spreadsheet

 

Hope this helps!

 
My CPU is a neural net processor, a learning computer.

42 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated May 10, 2024 Views 64,957 Applies to: