Solved

Get rid of Special Characters in Excel

Posted on 2010-11-16
15
375 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
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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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 descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now