Web Development

Ask something
Discussion Locked

find/replace issue

0 helpful
6 replies

by maniskid 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am trying to clean up some data that was put into an excel spreadsheet. The data is laced with those square blocks that typically are indicitave of broken data or spaces.

However, when I tried to use any of the many msoffice(2003) utilities to work with data, none of them would accept these square blocks as input.

For instance, I could not copy and paste one of them into the find/replace box, or any other similar utility.

I tried character map but those utilities don't accept different outputs (also, I think that the square blocks could be any of hundreds of fonts). The net result is that I cant do anything with them except work with them manually but there is way to much data.

I believe that the data was pulled from a filemaker pro data base (www.filemaker.com) and put into an excel spreadsheet. I have very little hope of finding the person who did this.

I would love to convert these, delete these, get them into a suitable entry box, or even be able to find out what they are. I am currently exploring filemaker client/server to use the drivers.

Any help would be greatly appreciated.

6 replies

by dryflies

You can save the data as comma delimited text and then use a filter to delete andthing that is not alphanumeric, a comma, or a CR/LF.

you may have to do this for each excel worksheet and then paste the data back in to the original sheet. or load the comma delimited sheet and paste the formulas.

by maniskid

That sounds like it would do the trick dryflies, I know how to save to csv or similar but how can I create such a filter?

by ahar

Now, thats the hard part.
For detailed infos take a look at Regular Expressions. For changing the files I whould suggest sed as a commandline-tool or you just take an editor and use the search-replace-function.

by juergen hartl

Try the easy first:
Those square blocks could just be caracters not present in your current Font (like ??? or asian characters). Try formatting in different fonts.

by maniskid

I found out that they are return characters. Such as when skipping a line, if you want to put a line in, this character is a substitute. (a formula of '=char(7)' prints one of these characters)

Also the '=clean(text)' formula seems to do the trick for some of the data, but I need them for long text files.

Now I have the problem that I can drop them into a word document and then back again (tedius as all getout) but excel2003 wants to bust up the paragraphs into predefined lengths down the page.

This discussion is closed to new replies.