How to remove square carriage returns from my cells

nonijones

New Member
Joined
Oct 11, 2007
Messages
14
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.
 
Upvote 0
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
 
Upvote 0
Carriage Returns

Thanks everyone for your assistance with this,

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

Thanks again!

NoniJones
 
Upvote 0
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.

Excel Workbook
AB
1BakedBeans on ToastBaked Beans on Toast
2SmokedSalmon in BrineSmoked Salmon in Brine
3Ice Cream CosmopolitanIce Cream Cosmopolitan
4MixedNutsper kiloMixed Nuts per kilo
5Baby ShampooFragrance FreeBaby Shampoo Fragrance Free
Substitute and Trim
 
Upvote 0
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.

 
Upvote 0
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??
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top