word 2010 vb script- how to trim the new line in string of the table cell

Question

Hi Everybody,

I am going to trim the New Line Char for the string in table cell. (From left-end and From right-end)

Here is my code

 txt = .Cell(t, 2).Range.Text
                    Do While Right(txt, Len(vbCrLf)) = vbCrLf
                        txt = Left(txt, Len(txt) - Len(vbCrLf))
                    Loop
                    
                    Do While Left(txt, Len(vbCrLf)) = vbCrLf
                        txt = Mid$(txt, Len(vbCrLf) + 1)
                    Loop
                    
                    Do While Right(txt, Len(vbCr)) = vbCr
                        txt = Left(txt, Len(txt) - Len(vbCr))
                    Loop
                    
                    Do While Right(txt, Len(Chr(13))) = Chr(13)
                        txt = Left(txt, Len(txt) - Len(Chr(13)))
                    Loop
                    
                    Do While Right(txt, Len(Chr(10))) = Chr(10)
                        txt = Left(txt, Len(txt) - Len(Chr(10)))
                    Loop
                    
                    Do While Left(txt, Len(vbCr)) = vbCr
                        txt = Mid$(txt, Len(vbCr) + 1)
                    Loop
                    .Cell(t, 2).Range.Text = txt

 

But it doesn't work. I still the new line break at the end of the string.

Could you advise me asap?

Thanks for your attention

---

Liki Crus

Answer 1

Try

            txt = .Cell(t, 2).Range.Text
            txt = Left(txt, Len(txt) - 1)

            Do While Right(txt, 1) = vbCr
                txt = Left(txt, Len(txt) - 1)
            Loop

            Do While Left(txt, 1) = vbCr
                txt = Mid$(txt, 2)
            Loop

            Do While Right(txt, 1) = Chr(11)
                txt = Left(txt, Len(txt) - 1)
            Loop

            Do While Left(txt, 1) = Chr(11)
                txt = Mid$(txt, 2)
            Loop

            .Cell(t, 2).Range.Text = txt

vbCr = Chr(13) is a paragraph break, and Chr(11) is a manual line break.

---

Regards, Hans Vogelaar