text to col with embedded mystery box

[Craig860]

Hi,
Im trying to use the text to col function in excel and have hit a snag.
My source data looks like this:
"DEPT OF SPECIAL SERVICES â–¡ 760 COOPER ST.â–¡ AGAWAM MA 01001"
I think these little boxes are CR returns because when I paste it as is in
word it appears line under line.
When I do the text to columns feature and I choose OTHER as the seperator I
can't insert this little box. Even when I do a find replace it doesn't see it.
I want it so each cell a, b, c has the name, street address, city and state
etc.
I even tried doing a find replace to change the box to something else.
Any ideas?

 

[Barb Reinhardt]

Try this to replace the ALT ENTER with a semicolon

Sub FindAltEnter()
Dim rng As Excel.Range, rng1 As Excel.Range
Set rng = Cells.Find(What:=Chr(10), _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
sAddr = rng.Address
Do
If rng1 Is Nothing Then
Set rng1 = rng
Else
Set rng1 = Union(rng1, rng)
End If
Set rng = Cells.FindNext(rng)
Loop Until rng.Address = sAddr
End If
If Not rng1 Is Nothing Then
rng1.Select
rng1.Interior.ColorIndex = 6
Else
MsgBox "None found"
End If
End Sub

 

[Craig860]

didn't work. "None Found"

Try this to replace the ALT ENTER with a semicolon

Sub FindAltEnter()
Dim rng As Excel.Range, rng1 As Excel.Range
Set rng = Cells.Find(What:=Chr(10), _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
sAddr = rng.Address
Do
If rng1 Is Nothing Then
Set rng1 = rng
Else
Set rng1 = Union(rng1, rng)
End If
Set rng = Cells.FindNext(rng)
Loop Until rng.Address = sAddr
End If
If Not rng1 Is Nothing Then
rng1.Select
rng1.Interior.ColorIndex = 6
Else
MsgBox "None found"
End If
End Sub

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

 

[Dave Peterson]

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.aspx

Depending on what that character is, you may be able to use alt-#### (from the
number keypad) to enter the character into the Other box in the text to columns
wizard dialog.

In fact, you may be able to select the character (in the formula bar), and copy
it. Then use ctrl-v to paste into that text to columns Other box.

If that fails, you could use a different technique to change those funny
character to a different (but type-able) character (like the broken vertical bar
|).

I saved this from a previous post. You may find it useful:

You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've
never been able to get alt-0013 to work for carriage returns.

Another alternative is to fix it via a formula:

=substitute(a1,char(##),"|")

Replace ## with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(##)) '<--What showed up in CellView?

myGoodChars = Array("|")

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

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

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Craig860]

Actually this post worked well I found on another message board. Thanks
anyway.
It can be found at: http://www.ozgrid.com/forum/showthread.php?t=20451

Here is a VBA alternative. Select the column that needs the replacement
applied to.
Now go to the VBE (ALT+F11) immediate window (CTRL+G) and enter the following,

Selection.Replace chr(13)," "

which will replace carriage_returns with a space.

Cheers
Andy

 

[ShaneDevenshire]

Hi,

Could be TAB delimited. When you pick Delimited and click next turn on all
the options and see if the preview displays something you want, if it does
you don't need to resort to other approaches.