Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Location
    Orange Cty New York
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    excel 97 - hard returns

    I have to do the following and don't have a clue how to do it a easy way:
    I have a spreadsheet that in some columns there are names and address separated by hard returns, is there any way that I can get rid of the hard returns

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel 97 - hard returns

    I don't know if I understand it right: Do you mean that some text in the same cell is separated by hard returns, that is ALT + ENTER? In that case, suppose that this text is in cell A2, then enter in cell e.g. B2, the worksheetfunction =CLEAN(A2), which removes all non-printable characters. Hope this helps.

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

    Re: excel 97 - hard returns

    If the Names and Addresses were in A1 to A100, then find an unused column, lets say D, and enter the following forumla in cell D1:

    <pre>=Clean(A1)
    </pre>


    Copy this formula down column D to D100 by grabing the handle in the lower right corner of D1 and dragging it down to D100. This should give you a column without the hard returns. Now select D1 through D100 and do Copy those cells. Select A1 and then select "Paste Special" from the Edit menu. In the resulting dialog box, click on "Values" in the Paste section and then click on OK. Column A should now contain the Names and Addresses without the hard return. You can now delete the formulas in column D.
    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
  •