stripping control characters in excel

[The Fool]

I often need to strip control characters from excel spreadsheets for data imports...(tabs carriage returns etc) . I do it at the moment by saving as a text file and sticking it on a unix box to do it....but I reeeeeely would like to be able to do it in windows, preferably in excel. Anyone got any ideas? Know of any little apps that strip control characters from xls cells?

 

[a_unique_person]

I'm surprised, given the amount of software on offer to do this, it appears you can't do it simply. A macro would be the way to do it, if VBA is not too daunting.

Which is surprising, since Word has the ability to do this.

BTW, I hear the Japanese make very reliable, cheap, and smooth motorbikes, if you're still looking.

 

[The Fool]

I'm surprised, given the amount of software on offer to do this, it appears you can't do it simply. A macro would be the way to do it, if VBA is not too daunting.

Which is surprising, since Word has the ability to do this.

BTW, I hear the Japanese make very reliable, cheap, and smooth motorbikes, if you're still looking.

Its a major pain. I get a bunch of spreadsheets dumped from someones database and have to shoehorn it into ours using the clunky import routine its saddled with. The free text notes fields are littered with every control character imaginable and I need a simple way to replace them with a nice shiny little space...without having to convert and ftp to and from a unix box......otherwise the imported data in our database looks like klingon poetry.

Here's an Idea...you could write me some VB to strip control characters from xls files and I can sell it and keep the money...how about it?

And there is nothing wrong with Japanese bikes but sometimes smooth and reliable is boring

 

[SezMe]

What is the file type you start with? Can you just dump it to a csv file then reimport it. Should get rid of all the other crap.

 

[mummymonkey]

This any use?

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[MRC_Hans]

I don't quite understand what you are trying to do. Excel can export spreadsheets in a dozen formats, some of them quite simple (CSV has already been mentioned), can't you use any of those?

Hans

 

[Cleon]

Export to CSV.
Run a Perl script to strip out the crap you don't want.
Load into Excel.

Ta-dah.

 

[varwoche]

Export to CSV.
Run a Perl script to strip out the crap you don't want.
Load into Excel.

Ta-dah.

Good idea however cell comments don't export to csv.

TF, you could save the file as XML and then run a script but that seems like a lot of manual hassle if this is a recurring task. Let me know what version of Excel you are using and I'll see if I can throw a quick and dirty vba routine together.