I have an Excel doc which I'm data-merging in InDesign. Some of the cells in Excel have multiple carriage returns. Data X Data Y Data Z This messes with the merge. Is there any way to find/replace in Excel on the Mac to achieve. Data Z Seems straightforward on Windows but not Mac.
Here's a simple, non-Excel way to get rid of all the line breaks in your text: Paste the data into TextEdit, put your cursor at the end of any line, then hold shift and press the right arrow once. This will select the invisible new line character. Now hit command + c to copy this invisible character, and then got to Edit Find Find and Replace. And search for your copied character and replace it everywhere with just a space. Now you shouldn't have any more line breaks in your text.:) (On a side note, you don't need to replace with a space you can replace it with another symbol if that may be more convenient for what you're doing in Excel.).
I need to change comments to cell contents to be able to export all the content of an Excel file to database programs. I found that does the trick, placing the comment in the adjacent cell. However, there are manual line breaks in the cell that I also wish to remove. I find find/replace solutions for Windows Excel (search for alt 0100) but nothing for Mac Excel (2011). So I'm wondering: How are the line breaks getting there? Are they part of the VBA code I used to convert the comments to cell content?
Or are they from the comments themselves? If they're from the VBA code, how could it be edited to replace line breaks with spaces? Thanks for any suggestions. Code: Sub CopyCommentText Dim CmtText As String Dim CmtCount As Integer, i As Integer Dim CommCell As String CmtCount = ActiveSheet.Comments.Count For i = 1 To CmtCount CommCell = ActiveSheet.Comments(i).Parent.Address Range(CommCell).Offset(0, 1) = Replace(ActiveSheet.Comments(i).Text, Chr(10), ' ') Next i End SubAlso notice, as with your substitute function, I changed it to add a space between the lines or Comment 1 In Cell A1 would turn into Comment 1In Cell A1 Now with adding a space it will be Comment 1 In Cell A1. Ah, thanks, that looks a lot clearer. The first try didn't work, so I compared with the =substitute function, which hadn't worked for me either (unlike =clean).
It turns out that the character in question is Chr(13) and not Chr(10). Thank you for all the helpful tips! Also learned the easiest way to fill down a function for non-contiguous cells: 1.
Type the range to be filled in the name box to the left of the function: 'C205:C20925'. For some reason, 'C1:C20925' was unacceptable to Excel; maybe it's because the first 204 cells were empty.
Type the function in C205: =substutite(B205, Char(13), ' ') 3. Press command-D (for Macs) (4. Select the whole column and copy & paste special with values only.).