Replace carriage return and line feed in excel

[Johnosley]

I'm using a multline text box in a userform in word to collect some information. This information is used in an excel workbook but once it's copy paste (using a macro) I have a square box in the cell. It seems to be a combination of carriage return (char(13)) and feed line (char(10)).

What I found so far it's how to remove that and/or replace those character by "-" or space. But I would like to keep the return but don't want to see the square box.

See my attached file for an example.

Thank you

[Crocus Crow]

Just replace the Chr(13) with "" then, like this:

Sub vbax()
    Dim multilineText As String
    multilineText = "Line 1" & Chr(13) & Chr(10) & "Line 2"
    Range("A1").Value = multilineText
    Range("B1").Value = Replace(multilineText, Chr(13), "")
End Sub

[slamet Harto]

=LEFT(A3,FIND(CHAR(10),A3)-2)&CHAR(10)&RIGHT(A3,FIND(CHAR(10),A3)-1) 

then Ctrl+1, go to Alignment and tick Wrap Text

[Johnosley]

Just replace the Chr(13) with "" then, like this:

Sub vbax()
    Dim multilineText As String
    multilineText = "Line 1" & Chr(13) & Chr(10) & "Line 2"
    Range("A1").Value = multilineText
    Range("B1").Value = Replace(multilineText, Chr(13), "")
End Sub

Sorry I didn't get back to you quickly. I worked on other issue on my vba code.

But in my example I put only 2 lines but in fact there is no limitation in terms of number of lines. I could be a paragraph or much more !

But my question is more how to replace character from word to get the equivalent to excel ! I will post a new tread for that.

Thanks for your help.

[GTO]

Rather than start a new thread reference the same problem, could you post an attached wb in .xls format with what gets imported and what you'd like. Please in .xls format, as many potential 'answerers' do not have access to 2007+ format.

[Johnosley]

Rather than start a new thread reference the same problem, could you post an attached wb in .xls format with what gets imported and what you'd like. Please in .xls format, as many potential 'answerers' do not have access to 2007+ format.

see attached a 2003 version of my before and after excel file.

Thanks

[Crocus Crow]

My first code was just an example showing how to call the Replace function to replace the characters to get rid of the square boxes; you need to call it in a loop for each cell. Also it looks like the square boxes are caused by just Chr(13), and Excel needs these to be replaced by Chr(10) to give a new line in a cell.

Sub vbax()
    Dim LR As Long, r As Long
    With Sheets("Sheet1")
    LR = .Cells(.Rows.Count, "A").End(xlUp).Row
    For r = 2 To LR
        .Cells(r, "C").Value = Replace(.Cells(r, "A").Value, Chr(13), Chr(10))
    Next
    End With
End Sub

I used the following code to determine which characters were present in the cells.

Sub Show_Chars()
    Dim i As Integer, s As String
    s = Cells(2, "A").Value
    Debug.Print s
    For i = 1 To Len(s)
    Debug.Print Mid(s, i, 1), Asc(Mid(s, i, 1))
    Next
End Sub

[Johnosley]

My first code was just an example showing how to call the Replace function to replace the characters to get rid of the square boxes; you need to call it in a loop for each cell. Also it looks like the square boxes are caused by just Chr(13), and Excel needs these to be replaced by Chr(10) to give a new line in a cell.

Sub vbax()
    Dim LR As Long, r As Long
    With Sheets("Sheet1")
    LR = .Cells(.Rows.Count, "A").End(xlUp).Row
    For r = 2 To LR
       .Cells(r, "C").Value = Replace(.Cells(r, "A").Value, Chr(13), Chr(10))
    Next
    End With
End Sub

Works great ! I added different lines for each character I wold like to replace. Thank you very much.