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

 

[Candyman8019]

If you want to use a formula, you could try something like: =SUBSTITUTE(A1,CHAR(10),",")

 

[MrRAMMounts]

If you want to use a formula, you could try something like: =SUBSTITUTE(A1,CHAR(10),",")

Hi Candyman. Thanks for the quick response. I have actually tried that one and it returns the same error saying that Excel cannot locate any line breaks (as represented by the 10 code).

Regards

 

[MrRAMMounts]

See image for reference

 

[Candyman8019]

have you tried =CLEAN(H2)
which should remove all non-printable characters.

 

[MrRAMMounts]

have you tried =CLEAN(H2)
which should remove all non-printable characters.

Yes, that does remove the line breaks/symbols, but just returns a long string of words with no indication of where a ", " should be in the address. Is there any way to use the CLEAN function to replace the cleaned items with a ", "?

Regards

 

[Candyman8019]

Can you edit one of the cells and select the non-printable character and do a CTRL+C to copy it. Then try do a simple replace, pasting the character that was just copied in the 'find what' box?

 

[MrRAMMounts]

Can you edit one of the cells and select the non-printable character and do a CTRL+C to copy it. Then try do a simple replace, pasting the character that was just copied in the 'find what' box?

I tried that, but you can't select anything from the actual cell, you have to do it in the preview box, but the preview box doesn't have a selectable line break. It only seems to be visible in the main cell (see Sample and Sample2).

Could the CLEAN function be used with IF perhaps to replace any found items?

 

[Candyman8019]

Can't you double click the cell to edit the contents directly?

 

[Candyman8019]

what I mean is double click the cell to edit the contents directly so you can copy that non-printable character in order to use it in the replace command.