Solved

Get rid of Special Characters in Excel

Posted on 2010-11-16
15
381 Views
Last Modified: 2012-05-10
Hi

Colleagues have an issue with exported data

[] is appearing in cells, randonly, not always at end, start etc....
Cant do find and replace on it

Is there a non-macro way to get rid of this
As they have tons of reports to go through?

Urgent! Please!
0
Comment
Question by:flickimp1717
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +2
15 Comments
 
LVL 5

Expert Comment

by:Pabilio
ID: 34145383
Hi Flickimp,

I think you can do what you need going by:
Edit > Replace

In the windows that opens in the field search write [] and leave the Replace with empty and click the Replace all button

It should do it.

Roberto.
0
 
LVL 5

Expert Comment

by:Pabilio
ID: 34145402
Probably you will need to select all sheet prior to do what I said.
Roberto.
0
 

Author Comment

by:flickimp1717
ID: 34145423
Hi

No, it doesnt recognise [] aka char(10)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:dazwillnot
ID: 34145441
Run it as two seperate Find and replaces.

One to replace [ with nothing and another to replace ] with nothing.

Works for me anyway.
0
 

Author Comment

by:flickimp1717
ID: 34145452
Hi

the [] is actually the carriage symbol? rectangle
0
 
LVL 5

Expert Comment

by:Pabilio
ID: 34145463
Could you post a sample of the exported data ?
I tested here and it works.
Roberto.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 34145484
Re-posting as expert comment.

Try using the SUBSTITUTE function:
=SUBSTITUTE(A1,CHAR(10),"")

Add a new column and put the formula in it.  Then drag down to remove all the char(10)s.  Then copy and pastespecial values and delete the original column.
0
 

Author Comment

by:flickimp1717
ID: 34145493
0
 

Author Comment

by:flickimp1717
ID: 34145503
had tried before

=SUBSTITUTE(A1,CHAR(10),"")


but some [] still existed
0
 
LVL 24

Accepted Solution

by:
broomee9 earned 500 total points
ID: 34145514
OK, use the CLEAN function then.

=Clean(G3)
Copy-of-test.xls
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34145520
Try a formula like this.  It strips tab (9), line feed (10), carriage return (13), and non-breaking space (160):

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(9)," "),CHAR(10)," "),CHAR(13)," "),CHAR(160)," "))
0
 
LVL 5

Expert Comment

by:Pabilio
ID: 34145544
Flickimo,
There is not [] in the data you posted.
?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34145548
Not a crtiticism of broomee9's approach, just noting the differences:

1) CLEAN removes the non-printing characters, whereas my approach replaces them with spaces (and I use TRIM to remove any resulting leading/trailing spaces, or multiple spaces internally)

2) CLEAN will not remove ANSI 160, the infamous non-breaking space

Whichever approach fits your need best, you are in the best position to judge :)

Patrick
0
 

Author Closing Comment

by:flickimp1717
ID: 34145559
brilliant
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question