Excel 2003 imported Outlook contacts have nonprinting characters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Circe - 05 Feb 2008 21:26 GMT

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.

Reply to this Message

Gord Dibben - 05 Feb 2008 23:34 GMT

Try edit>replace

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

>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.

Reply to this Message