Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vba excel replace text with special characters (vba/excel/2000)

    After importing a text file in excel 2000 (using VBA), I want to insert a "new line" character in select cells.

    Using MS WORD, the find/replace function supports special characters. How can this be done using excel/VBA?

    For example, if a single cell contains the following text:
    My sentence 1. My Sentence 2. My Sentence 3.

    I want the cell to look like this (cell format using "wrap text" option) :

    My sentence 1.

    My Sentence 2.

    My Sentence 3.


    *********************************
    Thanks in advance, John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba excel replace text with special characters (vba/excel/2000)

    This should get you started:

    <pre> Worksheets("Sheet1").Range("A1").Replace ".", "." & Chr(10)
    </pre>


    This may be closer to what you really need:

    <pre> While InStr(Worksheets("Sheet1").Range("A1").Value, ". ")
    Worksheets("Sheet1").Range("A1").Replace ". ", "."
    Wend
    Worksheets("Sheet1").Range("A1").Replace ".", "." & Chr(10)
    If Right(Worksheets("Sheet1").Range("A1").Value, 1) = Chr(10) Then
    Worksheets("Sheet1").Range("A1").Value = Left(Worksheets("Sheet1").Range("A1"), Len(Worksheets("Sheet1").Range("A1")) - 1)
    End If
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •