Remove line breaks with VBA?

[Feirefiz]

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 VBA code 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.

 

[rjwebgraphix]

Depends on the line break. The most common is Chr(10). A find and replace of Chr(10) should do the trick on a MAC too. The uncommon line break is Chr(13). In some cases, as I recently found out, they can be combined and have both Chr(10) and Chr(13) back to back. The old DOS editor did line feeds that way and drove me nuts for a day. Although, more likely than not, you're just talking about Chr(10).

 

[rjwebgraphix]

I didn't look through the code you linked to, but this is a line from my recent code that removes Chr(10) from a string.

DizData is just a string variable, but I'm sure you could use it in your comment copying code.

DizData = Replace(DizData, Chr(10), "")

 

[Feirefiz]

Thanks for the suggestions. I tried find/replace with "Chr(10)" and "Chr(13)", and it doesn't find anything. So that doesn't work.

I'd never even looked at VBA till yesterday, so I'm haphazardly guessing on how to insert your line into the code to see if that works. It doesn't work, so I'm assuming I'm doing it wrong.

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) = ActiveSheet.Comments(i).Text

DizData = Replace(DizData, Chr(10), "")
Next i
End Sub

The fact that this editor won't let me remove "[/code]

" so the code above would show as one piece makes me think even more that it's wrong.

 

[Feirefiz]

New search ideas left me with two functions that would do the trick (at least in my case):

1. =substitute(A1, Char(10), " ")

or

2. =clean(A1)

But since the column to be cleaned doesn't have contiguously filled cells, I can't fill down. Is there a way around this?

 

[rjwebgraphix]

I think if you're already running code, just have the code do it for you.... try this. See how it varies from your code. Dizdata was my string name, You just needed to adjust it to fit your 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 Sub

Also 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

 

[Feirefiz]

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.
2. 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.)