Results 1 to 6 of 6

Automatic Replace with a RETURN (ALT-ENTER)

This is a discussion on Automatic Replace with a RETURN (ALT-ENTER) within the Excel Questions forums, part of the Question Forums category; I would like to find a character and replace it automatically with a Return so as to format my cells. ...

  1. #1
    Board Regular
    Join Date
    May 2003
    Location
    Houston, Texas
    Posts
    68

    Default Automatic Replace with a RETURN (ALT-ENTER)

    I would like to find a character and replace it automatically with a Return so as to format my cells.
    I know I can manually enter a Return by using the ALT-ENTER combination but I don't know how to do that automatically (in Word, we can replace by ^p but this does not work in Excel).

    Any suggestion would be appreciated.

    Hervé Chain
    Houston, TX
    Hervé

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677

    Default

    I was able to do it in a quick macro (replacing d with a carriage return). Try on a copy first.


    Cells.Replace What:="d", Replacement:=Chr(10), LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    K

  3. #3
    Board Regular
    Join Date
    May 2003
    Location
    Houston, Texas
    Posts
    68

    Default Re: Automatic Replace with a RETURN (ALT-ENTER)

    You are my hero for the day!

    Thanks a lot

    Hervé
    Hervé

  4. #4
    New Member
    Join Date
    Mar 2009
    Posts
    1

    Talking Re: Automatic Replace with a RETURN (ALT-ENTER)

    Hi,

    I need to know how to use this macros in excel.

    pls suggest with stepssss

  5. #5
    New Member
    Join Date
    Apr 2009
    Posts
    1

    Default Re: Automatic Replace with a RETURN (ALT-ENTER)

    We had a long string of data separated by semicolons in each cell. The user wanted the data to start on a new line after each semicolon. We searched for "; " and replaced it with "[control]-J". Success.

  6. #6
    New Member
    Join Date
    Dec 2012
    Posts
    17

    Default Re: Automatic Replace with a RETURN (ALT-ENTER)

    Quote Originally Posted by kkknie View Post
    I was able to do it in a quick macro (replacing d with a carriage return). Try on a copy first.


    Cells.Replace What:="d", Replacement:=Chr(10), LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    K
    I tried this on my file with 5000 rows and paragraphs within these with "GDT" to be replaced by a carriage return. But it stops after one thousand rows or so. Running the macro again doesn't make it continue where it left off.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com