Solved

Get rid of Special Characters in Excel

Posted on 2010-11-16
15
382 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Date Question 8 41
Vlookup Help 3 25
Custom fill series 12 33
count number 10 27
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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