text wrap problem

[lou_1977]

Hi all

I'm importing address info into Excel from our in-house database. Th
addresses comes as a block with line breaks which show up as a squar
in Excel (when text wrap is off). I need to change these marks int
commas. Is there any way to do this automatically?

I dont have VBA knowledge but suspect this is where the answer lies.

Apologies if this is an innane question to some but Im stumped.

Cheers
Lo

 

[Dave Peterson]

From a previous post:


Chip Pearson has a very nice addin that can help identify those funny
characters.

http://www.cpearson.com/excel/CellView.htm

If you have to do this lots of times, I'd record a macro when you do an
Edit|Replace and modify it to do those funny characters:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13))

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next iCtr

End Sub

Just keep adding those Hex codes you found using Chip's addin.

(I changed the funny characters to "". You could use " " if you wanted a
space.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

========
With some of those funny characters (alt-enters or alt-0010), you can actually
use edit|replace.

Edit|replace
hit and hold the alt key while typing 0010 (from the number keypad--not above
QWERTY).

It may look like nothing got entered in that little box, but try it.

And if this is an alt-enter, you may just want to change the text wrap.
Format|Cells|alignment tab.

It's a way of entering more than one line inside a cell.