How to remove square carriage returns from my cells

[nonijones]

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

 

[LGHT]

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?

Not sure what that is suppose to do, but the cell I tested on is e11 so I tried

=CODE(MID(e11,5,1)) and it gave me the number 11???

 

[LGHT]

I was able to get rid of the character with =CLEAN(E11), but now the 2 words don't have a space in between. Does anyone know how to include a space where the weird character was removed? I have about 8,000 lines in this spreadsheet

 

[GTO]

I was able to get rid of the character with =CLEAN(E11), but now the 2 words don't have a space in between. Does anyone know how to include a space where the weird character was removed? I have about 8,000 lines in this spreadsheet

Greetings Noni,

Not sure how you got a decent return on my test, as I now cannot fathom why I picked 5 as the start. Anyways, for the moment, let's say you got it to return the correct character, which is a vertical tab.

Now try Peter's formula for CHAR(11)

In whatever cell: =TRIM(SUBSTITUTE(E1,CHAR(11)," "))

If that results in "Baby Shampoo Fragrance Free," then by golly, I think we've found the guilty character.

Mark

 

[imfritzy]

I'm somewhat new to Excel (2003), so I need step-by-step help. I've got a spreadsheet into which I've imported data into a column. The column has 200 rows of cells, each of which has four names separated by the little boxes (carriage returns, enters, or vertical tabs). I would like to remove the boxes from each cell, in one step if possible, and leave the four names separated but still within each cell.

What do I do? Any help is appreciated. (If you tell me to write some code, I won't know where to put it or what to do with it, but I'm open to any ideas.