• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

Get rid of Special Characters in Excel

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
flickimp1717
Asked:
flickimp1717
  • 5
  • 4
  • 2
  • +2
1 Solution
 
PabilioCommented:
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
 
PabilioCommented:
Probably you will need to select all sheet prior to do what I said.
Roberto.
0
 
flickimp1717Author Commented:
Hi

No, it doesnt recognise [] aka char(10)
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
dazwillnotCommented:
Run it as two seperate Find and replaces.

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

Works for me anyway.
0
 
flickimp1717Author Commented:
Hi

the [] is actually the carriage symbol? rectangle
0
 
PabilioCommented:
Could you post a sample of the exported data ?
I tested here and it works.
Roberto.
0
 
TracyVBA DeveloperCommented:
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
 
flickimp1717Author Commented:
0
 
flickimp1717Author Commented:
had tried before

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


but some [] still existed
0
 
TracyVBA DeveloperCommented:
OK, use the CLEAN function then.

=Clean(G3)
Copy-of-test.xls
0
 
Patrick MatthewsCommented:
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
 
PabilioCommented:
Flickimo,
There is not [] in the data you posted.
?
0
 
Patrick MatthewsCommented:
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
 
flickimp1717Author Commented:
brilliant
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now