[help] VBA Replace in Text won't finish at the end of text

[maulana]

Hi,
i want try to change some character in one cell with replace functions in VBA to another cell.

every rows seem ok but at 13th row.
i miss full code. not fully code can't be replace and severed at the end of code.

Dim p As String
 
 Set a = ActiveWorkbook.Sheets("temp")
 i = 1
 
 While a.Cells(i, 5).Value <> ""
     p = a.Cells(i, 5).Value
     
     p = Replace(p, "+", "|+|", vbTextCompare)
     p = Replace(p, "-", "|-", vbTextCompare)
     p = Replace(p, "(", "|(|", vbTextCompare)
     p = Replace(p, ")", "|)|", vbTextCompare)
     p = Replace(p, "!", "!|", vbTextCompare)
     p = Replace(p, ";", "|;|", vbTextCompare)
     p = Replace(p, "&", "|&|", vbTextCompare)
     p = Replace(p, "||", "|", vbTextCompare)
     a.Cells(i, 6).Value = "'" & p
     i = i + 1
 Wend
 
 
 End Sub

here is my workbooks.
http://www.filehosting.org/<wbr>file/details/160732/max_text.<wbr>xls

 

[maulana]

http://www.ziddu.com/download/11083560/maxtext.xls.html

in case you don't wanna put your email

this my workbook with 30000 character and i want replace some character into other cell

thanks.

 

[John_w]

The maximum number of characters in a cell is 32767. Your 13th row result is longer than that so it truncates. To put the remaining characters in the next column, replace:

     a.Cells(i, 6).Value = "'" & p

with:

        a.Cells(i, 6).Value = "'" & Left(p, 32766)
        If Len(p) > 32766 Then
            a.Cells(i, 7).Value = "'" & Mid(p, 32767, Len(p))
        End If

 

[maulana]

wow.. cool..

thanks john! really appreciate.