Removing double spacing (multiple CR's) from a group of cells

[ou81aswell]

I have a colleague in Paris who is running Excel 2010 under Windows/ Parallels on a Mac.

He has a number of cells which contain double spaced addresses (multiple carriage returns).

Example:

123 Main Street

Atlanta
Georgia

He's trying to remove the double spacing so it turns out like:

123 Main Street
Atlanta
Georgia


On my computer I can use Excel's replace feature and specify that it replace 2 CR's with a single CR by holding down the Alt key and typing 013 on the numeric keypad to specify the carriage return character. We can't figure out how to do this on a Mac since it doesn't have a numeric keypad.

Is there a macro that I could build that would do this automatically for the currently selected cells in Excel?

Alternatively, does anyone know how to enter ascii key codes via the Mac keyboard under Parallels?

Thanks.

 

[patel45]

123 Main Street

Atlanta
Georgia

you have in a single cell ?

 

[ou81aswell]

123 Main Street

Atlanta
Georgia

you have in a single cell ?

Yes. The cell contains carriage returns (chr(13)) and I want to be able to replace all chr(13) & chr(13) with chr(13) for the currently selected Excel cells.

 

[patel45]

try this code

Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
s = Chr(10) & Chr(10)
For j = 1 To LR
  Cells(j, 1) = Replace(Cells(j, 1).Text, s, Chr(10))
Next
End Sub

if it does not solve need a sample file for testing

 

[ou81aswell]

Thanks.