Replacing Carriage Return

Question

I am trying to export address information from our Accounting Software so that I can import it into our Shipping Software.

In our Accounting Software, the address information is contained in one box (no line 1, line 2, etc...) and you hit control-return to add a new line.  When I export an address is more than one line into Excel, it looks like this:

I have tried doing a find and replace function with both Control-010 and Alt-010, but this is what I get:

Any ideas on why it replaces one of the characters (box with ? in it), but not the other one?

Answer 1

Select the range you want to convert.

Press Alt+F11 then Ctrl+G to activate the Immediate window in the Visual Basic Editor.

Type or copy / paste the following line:

Selection.Replace vbCr, ""

With the insertion point anywhere in that line, press Enter.

Switch back to Excel.

Answer 2

Hi,

Try this

1. Copy the data to Notepad
2. Copy the special character in Notepad and press Ctrl+H
3. Press Ctrl+V in the Find what box
4. Click on Replace All

Hope this helps.

Answer 3

Unfortunately, neither of these worked for me.  This is what I get when I try to do the visual basic idea:

A1 is the original

A3 is the original copies and then the visual basic applied to it:

It looks like that creates a multi line (within the same cell) entry, and there is still one of those special characters.

Answer 4

Try the following (using the example from your screenshot):

In B1, enter the formula =CODE(MID(A1,17,1))

In C1, enter the formula =CODE(MID(A1,18,1))

What is the result of these formulas?

Answer 5

Try the following (using the example from your screenshot):

 

In B1, enter the formula =CODE(MID(A1,17,1))

In C1, enter the formula =CODE(MID(A1,18,1))

 

What is the result of these formulas?

B1 = 13

C1 = -10