My problem is that the data...

There is no problem. Simply embed your code thus using the cal
method:

Sub TextToColumn()
Dim c As Range
Dim i As Integer

For Each c In Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row
'assuming data is inCol A
For i = 1 To Len(c)
If Mid(c, i, 1) = Chr(32) And Mid(c, i + 1, 1) = "0" Then
k = k & Mid(c, i, 1) & Chr(39)
Else
k = k & Mid(c, i, 1)
End If
Next
c.Value = k
k = ""
Next
CALL YOURCODENAMEASAPPLICABL
End Su

--
Myle
-----------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...fo&userid=2874
View this thread: http://www.excelforum.com/showthread.php?threadid=57379