Hi

I have imported an address from outlook and pasted it into on cell in
excel. The line of text includes the carriage return boxes.

How can change my code to replace the boxes for a space.

ActiveCell.Offset(0, 1).Select
Selection = .BusinessAddress


Thanks

Adam,

First of all, it is better not to Select a cell and then work with
Selection. Instead, work with the cell directly. E.g.,

ActiveCell.Offset(0,1).Value = .BusinessAddress

You can use the Replace function to replace the line break character(s) with
spaces:

ActiveCell.Offset(0,1).Value = Replace(.BusinessAddress, Chr(10), " ")

What value you need with Chr depends on what character (10, 13, or both 10
and 13 together) is in the BusinessAddress string. If you put it in a cell,
you can use my Cell View add-in to see what character(s) is represented by
the box character. E.g.,

Range("A1").Value = .BusinessAddress

and then view the contents of A1 with CellView.
www.cpearson.com/excel/cellview.htm

Or, you could just do two Replace calls.

Dim S As String
S = .BusinessAddress
Replace(Replace(S, Chr(10), " "), Chr(13), " ")
ActiveCell.Offset(0,1).Value = S

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)




"Adam" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi
>
> I have imported an address from outlook and pasted it into on cell in
> excel. The line of text includes the carriage return boxes.
>
> How can change my code to replace the boxes for a space.
>
> ActiveCell.Offset(0, 1).Select
> Selection = .BusinessAddress
>
>
> Thanks
>