Help! I need to get rid of hard returns!
Prep for a pre-employment Excel test with Job Test Prep
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
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 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

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
  •  

 

 
DMCA.com