Excel 2003 imported Outlook contacts have nonprinting characters

  • Thread starter Thread starter Circe
  • Start date Start date
C

Circe

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Replace nonprinting character 2
Remove nonprintable characters from text in Access 2
The data in a linked Excel spreadsheet column is truncated to 255 characters in an Access database 0
CLEAN expression for Access to remove nonprinting characters? 2
Deleting LF Characters in cells 9
How to delete a 'return' symbol in cell for data thats been import 5
How do i name a range of data in excel 1
Formula results display only after pressing Enter key 1

Back
Top