Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Get rid of Special Characters in Excel

Posted on 2010-11-16
15
Medium Priority
?
389 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
Technology Partners: 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: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

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.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

705 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