Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Get rid of Special Characters in Excel

Posted on 2010-11-16
15
Medium Priority
?
395 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
  • 5
  • 4
  • 2
  • +2
14 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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:Tracy
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:
Tracy earned 2000 total points
ID: 34145514
OK, use the CLEAN function then.

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

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 93

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

578 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