I need to remove nonprinting characters that appear from an imported Outlook
contacts spreadsheet. Find and replace is not helpful and I am not sure how
to do this with formulas and maintain the values. Any ideas.

Try editreplace

what: Alt + 0160 (on the numpad)

with: nothing or a space

If no joy, post back or hang around for more replies.

You could try this macro which replaces with spaces.

Sub Remove_CR_LF()
With Selection
..Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub

Change =Chr(32) to ="" if you want no space.


Gord Dibben MS Excel MVP

On Tue, 5 Feb 2008 13:26:33 -0800, Circe
wrote:

I need to remove nonprinting characters that appear from an imported Outlook
contacts spreadsheet. Find and replace is not helpful and I am not sure how
to do this with formulas and maintain the values. Any ideas.