# 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. ## 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. 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.

3. 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. ## Carriage Returns

Thanks everyone for your assistance with this,

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

Thanks again!

NoniJones

5. 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

 * A B 1 Baked Beans on Toast Baked Beans on Toast 2 Smoked Salmon in Brine Smoked Salmon in Brine 3 Ice Cream Cosmopolitan Ice Cream Cosmopolitan 4 Mixed Nuts per kilo Mixed Nuts per kilo 5 Baby Shampoo Fragrance Free Baby Shampoo Fragrance Free

 Cell Formula B1 =TRIM(SUBSTITUTE(A1,CHAR(10)," "))

Excel tables to the web >> Excel Jeanie HTML 4

6. ## 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. ## 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. ## 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. ## 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. ## Re: How to remove square carriage returns from my cells

Originally Posted by Armando Montes
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 Last

#### Posting Permissions

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