Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Help! I need to get rid of hard returns!

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have downloaded data from Filemaker Pro to a text tab delimited file, and imported into XL. My problem is I have little squares where hard returns are in the file. I need to get rid of these and replace it with a space. I have tried find and replace, but it doesn't seem to work. In help I have discovered the clean function, but I don't understand pivot tables, so it is confusing. Please make directions as simple as possible for this beginner!
    Thanks!
    Christine

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-11 11:15, Christine D wrote:
    I have downloaded data from Filemaker Pro to a text tab delimited file, and imported into XL. My problem is I have little squares where hard returns are in the file. I need to get rid of these and replace it with a space. I have tried find and replace, but it doesn't seem to work. In help I have discovered the clean function, but I don't understand pivot tables, so it is confusing. Please make directions as simple as possible for this beginner!
    Thanks!
    Christine
    Christine,

    =SUBSTITUTE(A1,CHAR(13)," ")

    will replace carriage returns in A1 with spaces.

    Aladin

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, Aladdin. I have tried and I still have those little squares. Anything else I can try?

    Christine

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Christine, let's see what those "little squares" actually are. In an empty cell enter, =CODE(RIGHT(A1)), where A1 is a reference to one of those pesky text strings. What's the value that's returned?

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    11 is what shows up.

    ??

    Christine

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-11 11:51, Christine D wrote:
    11 is what shows up.

    ??

    Christine
    Okay, use...

    =SUBSTITUTE(A1,CHAR(11)," ")

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yay!!! Thank you so much!

    Christine

Some videos you may like

User Tag List

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
  •