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

    replace text with special characters (2000/9.0.2720)

    After importing a text file in excel 2000, 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 or 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
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: replace text with special characters (2000/9.0.2720)

    For each cell ou want to find . and replace with . chr(10)

    chr(10) gives you the soft return within a cell.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: replace text with special characters (2000/9.0.2720)

    Select the cell.
    Use Ctrl-H to bring up Excel's find and replace Dialog.
    In the 'find', type the fullstop.
    Tab to the 'replace with' box,
    Hold the Alt key and type 010 on your numeric keypad. You'll see nothing appear, but don't worry.
    Select 'replace All'
    Voilla!

    regards

    zeddy

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston suburb (Acton), Massachusetts, USA
    Posts
    109
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: replace text with special characters (2000/9.0.2720)

    Hey - That GREAT! I will use this a lot.

    Is there any way to "type it in" as I type the contents?

    e.g., something like >> P A R A 1 [Space] 1 [Alt+0] [Alt+1] [Alt+0] P A R A 2 ???

    I can add a macro LineBreak to the normal template - but it would usually be more convenient to just type it in...

    Thanks, thanks, thanks

  5. #5
    New Lounger
    Join Date
    May 2001
    Location
    New Jersey, New Jersey, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: replace text with special characters (2000/9.0.2720)

    Alt-Enter puts a 'hard carriage return' in a cell (its in the Help file under 'entering text'

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston suburb (Acton), Massachusetts, USA
    Posts
    109
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: replace text with special characters (2000/9.0.2720)

    I was lazy - I didn't even think to check "help" - boo on me. The idea that "it couldn't be done" had just seeped into my subconscious over the years. You were right - it was easy to find in help. Thanks

Posting Permissions

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