Text to Column

J

Jeff

Hello,

I have the following text in column A, which is one line:

:Â! 10428712 ! NAME LASTNAME ! 20092004 ! 00458921 ! PMIAVW !
Is it possible with a VBA macro to create a column everytime there's "!" and
to delete ":Â"
Thanks for any help,
 
D

Dave Peterson

You're going to have to find out what those characters are.

I like Chip Pearson's addin: CellView
http://www.cpearson.com/excel/CellView.htm
to tell me those funny characters.

Then you could use a macro to clean them up:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(yy) & Chr(zz))

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

End Sub

replace yy and xx with what Chip's addin shows.

And then record a macro while you
select the column
data|text to columns, delimited by !

And you'll have your code.
 
Top