Page 1 of 2 12 LastLast
Results 1 to 10 of 14

How to remove square carriage returns from my cells

This is a discussion on How to remove square carriage returns from my cells within the Excel Questions forums, part of the Question Forums category; Hello everyone, I have a worksheet containing square carriage return symbols (see below). EXAMPLES Eg1. Baked[]Beans on []Toast Eg2. Smoked[][]Salmon ...

  1. #1
    New Member
    Join Date
    Oct 2007
    Location
    Sydney
    Posts
    14

    Default How to remove square carriage returns from my cells

    Hello everyone,

    I have a worksheet containing square carriage return symbols (see below).

    EXAMPLES
    Eg1. Baked[]Beans on []Toast
    Eg2. Smoked[][]Salmon in Brine
    Eg3. Ice[][][] Cream [][]Cosmopolitan
    Eg4. Mixed[]Nuts[][]per kilo[][][][]
    Eg5. [][]Baby Shampoo[]Fragrance Free

    This data was extracted from an SQL Database and dumped into Excel.

    I would like to know if someone can suggest a script to replace all occurrences of [] with a space.

    Once I perform this, I can then perform a Search/Replace function as follows:

    Replace 4 spaces with 1 Space
    Replace 3 spaces with 1 Space
    Replace 2 spaces with 1 Space

    Hopefully then, the final result will appear as follows:

    Eg1. Baked Beans on Toast
    Eg2. Smoked Salmon in Brine
    Eg3. Ice Cream Cosmopolitan
    Eg4. Mixed Nuts per kilo
    Eg5. Baby Shampoo Fragrance Free

    Eg5. is a little tricky because a space will appear at the beginning of the cell (see above). It would be good if there's a script to remove occurrences of this also.

    I hope this makes sense.

    I would appreciate ANY assistance with this....

    With thanks,


    NoniJones

  2. #2
    MrExcel MVP Oaktree's Avatar
    Join Date
    Jun 2002
    Location
    Chicago, IL
    Posts
    7,742

    Default

    If it's really a carriage return, =SUBSTITUTE(A1,CHAR(10),"") should work. If not, try using =CODE(MID(A1,5,1)) where 5 is the position of the mystery character in cell A1's value and use =SUBSTITUTE(A1,CHAR(*result of CODE function goes here*),"")

    You could also try using edit-->REPLACE to replace (hold ALT and press 010) with nothing.
    "The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell

  3. #3
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default

    try
    Code:
    Sub test()
    Dim r As Range
    On Error Resume Next
    With CreateObject("VBScript.RegExp")
        For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants,2)
            .Pattern = "[\n\r]+"
            .Global = True
            r.Value = .replace(r.Value," ")
            .Pattern = "\s{2,}"
            .Global = True
            r.Value = .replace(r.Value, " ")
        Next
    End With
    End Sub

  4. #4
    New Member
    Join Date
    Oct 2007
    Location
    Sydney
    Posts
    14

    Default Carriage Returns

    Thanks everyone for your assistance with this,

    With your assistance, I have managed to work this out... finally!!

    Thanks again!

    NoniJones

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    26,067

    Default

    Noni

    Sounds like you got your result and I am not sure which method you used. For what it's worth, here's a slight modification of Oaktree's suggestion that does it all in one formula.

    Substitute and Trim

    *AB
    1Baked
    Beans on
    Toast
    Baked Beans on Toast
    2Smoked

    Salmon in Brine
    Smoked Salmon in Brine
    3Ice


    Cream

    Cosmopolitan
    Ice Cream Cosmopolitan
    4Mixed
    Nuts

    per kilo



    Mixed Nuts per kilo
    5

    Baby Shampoo
    Fragrance Free
    Baby Shampoo Fragrance Free

    Spreadsheet Formulas
    CellFormula
    B1=TRIM(SUBSTITUTE(A1,CHAR(10)," "))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  6. #6
    New Member
    Join Date
    Feb 2009
    Posts
    13

    Default Re: How to remove square carriage returns from my cells

    Trying to fix a long time problem where I have
    a wierd square on some of my cells. The file was exported from an old version of file maker pro ver. 5.0

    I've tried the 2 above options, but neither did a thing. The macro sits for about 2 minutes and seems as if it's working, but the squares are still there.

    I also tried this macro, but it does nothing.

    Sub RemoveNL()
    '
    ' RemoveNL Macro shared by FrankBaris.com
    '
    s = vbNewLine
    r = " "
    Cells.Replace What:=s, Replacement:=r
    '
    End Sub


    Can anyone help with this problem? I've searched online for hours and nothing has worked.


  7. #7
    New Member
    Join Date
    Feb 2009
    Posts
    13

    Default Re: How to remove square carriage returns from my cells

    I noticed that I can copy the character change the font size of it and the font itself, but when I try and paste it into word it seems as if it just puts in a return. Seems like the square is a hard return code or something? I couldn't copy and replace the character either??

  8. #8
    GTO
    GTO is offline
    Board Regular
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    5,028

    Default Re: How to remove square carriage returns from my cells

    Greetings,

    I'm not much on formulas, but for kicks, lets say "[][]Baby Shampoo[]Fragrance Free" is in A1. Try the formula: =CODE(MID(A1,5,1))

    What is the result?

  9. #9
    Board Regular
    Join Date
    Sep 2008
    Posts
    505

    Default Re: How to remove square carriage returns from my cells

    Try this:
    1. Select and copy the square
    2. Paste it in find
    3. Replace with empty

  10. #10
    New Member
    Join Date
    Feb 2009
    Posts
    13

    Default Re: How to remove square carriage returns from my cells

    Quote Originally Posted by Armando Montes View Post
    Try this:
    1. Select and copy the square
    2. Paste it in find
    3. Replace with empty

    It's a hidden character and can't be copied and pasted. I belive it's a hard return code of some type.

Page 1 of 2 12 LastLast

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