Unable to remove 'line breaks' in cells containing addresses

[MrRAMMounts]

Hi

This has flummoxed me. I have attached two images showing how the address details are displayed in the cells, and how they are displayed in the drop down preview section.

As you can see there is the odd square symbol representing a line break in the main cell section and the line break in effect in the preview section. (I exported this data from our old database system that has the addresses listed like this).

No matter what I do, I cannot seem to remove this symbol (en masse), short of going into each cell, going to each line break then pressing backspace, then space.

As I have several hundred lines to go through, I cannot do this manually.

I have found several suggested solutions including Find and Replace using the 'Ctrl+J' function to search for line breaks and replace them with ", ". I have also tried using complicated formulae, but no matter what I try excel keeps returning the error message that it can't find any line breaks (see attached image ). It is not a protected document and isn't set to view only or anything like that.

I have also tried the remove all formatting function and it doesn't make any different, or remove the line breaks, or square symbols.

Does anyone have any suggestions as to why this may be happening?

Regards

Jonathan

 

[rlv01]

If you run this macro on the active cell, it should tell you what non-printable character(s) you are dealing with

Sub CellCharList()
    Dim S As String, Ch As String
    Dim CharStr As String, AscStr As String
    Dim I As Long
    Dim Printables As String
    
    'Build set of printable characters
    For I = 32 To 126
        Printables = Printables & Chr(I) & ","
    Next I
    
    With ActiveCell
        S = .Value
        For I = 1 To Len(S)
            Ch = Mid(S, I, 1)
            If InStr(1, Printables, Ch & ",") > 0 Then
              CharStr = CharStr & Ch
            Else
            CharStr = CharStr & "<" & Right("00" & Application.WorksheetFunction.Dec2Hex(Asc(Ch)), 2) & ">"
            End If
        Next I
    End With
    
    Debug.Print "---"
    Debug.Print S
    Debug.Print CharStr

    MsgBox CharStr
End Sub

 

[Candyman8019]

That is excellent rlv01. I was looking for something like that Thanks.

 

[MrRAMMounts]

Hi.

Double clicking on the cell expands the cell to a similar view as the preview box at the top, but when done the little symbols disappear and there is nothing to select. See attached images.

 

[MrRAMMounts]

If you run this macro on the active cell, it should tell you what non-printable character(s) you are dealing with

Sub CellCharList()
    Dim S As String, Ch As String
    Dim CharStr As String, AscStr As String
    Dim I As Long
    Dim Printables As String
   
    'Build set of printable characters
    For I = 32 To 126
        Printables = Printables & Chr(I) & ","
    Next I
   
    With ActiveCell
        S = .Value
        For I = 1 To Len(S)
            Ch = Mid(S, I, 1)
            If InStr(1, Printables, Ch & ",") > 0 Then
              CharStr = CharStr & Ch
            Else
            CharStr = CharStr & "<" & Right("00" & Application.WorksheetFunction.Dec2Hex(Asc(Ch)), 2) & ">"
            End If
        Next I
    End With
   
    Debug.Print "---"
    Debug.Print S
    Debug.Print CharStr

    MsgBox CharStr
End Sub

Hi

Thanks for the suggestion, but I am not that advanced in Excel to be able to use VBA code and macros and stuff. Also, it appears that the symbol is the [CHAR 10] one to represent a line break. The issue is that although it is there in the cells in general, when a cell is double clicked (or the preview cell is expanded at the top) the symbol disappears and the line breaks are inserted which can't then be selected.

 

[MrRAMMounts]

If you run this macro on the active cell, it should tell you what non-printable character(s) you are dealing with

Sub CellCharList()
    Dim S As String, Ch As String
    Dim CharStr As String, AscStr As String
    Dim I As Long
    Dim Printables As String
   
    'Build set of printable characters
    For I = 32 To 126
        Printables = Printables & Chr(I) & ","
    Next I
   
    With ActiveCell
        S = .Value
        For I = 1 To Len(S)
            Ch = Mid(S, I, 1)
            If InStr(1, Printables, Ch & ",") > 0 Then
              CharStr = CharStr & Ch
            Else
            CharStr = CharStr & "<" & Right("00" & Application.WorksheetFunction.Dec2Hex(Asc(Ch)), 2) & ">"
            End If
        Next I
    End With
   
    Debug.Print "---"
    Debug.Print S
    Debug.Print CharStr

    MsgBox CharStr
End Sub

I managed to input and run the macro. It returned this result on the target cell (attached). I tried searching for this code (<0B>) in the FIND/REPLACE function, but it didn't find anything.

Is there a way to utilise this code to replace the symbols?

Thanks for the help.

 

[MrRAMMounts]

I Should also mention that "wrapping text" on any cell in the column with the weird symbols doesn't do anything.

 

[rlv01]

0B (011) is the vertical tab character. Your best way to replace it might be to use VBA.