Cleaning Messy Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 11 Dec 2007 16:16 GMT

I’m trying to fix a messy imported data set. The CLEAN formula removes
unprintable characters, but also removes separation between multiple words.
Is there a way to replace unprintable characters with spaces or delimiters
such as commas? Thanks.

Signature

Jay

Reply to this Message

Pete_UK - 11 Dec 2007 16:25 GMT

You could use the SUBSTITUTE formula to replace some text (eg
character code 160) with some other text (eg space or comma, or both).
Further details can be found in Excel Help.

Hope this helps.

Pete

> I'm trying to fix a messy imported data set. The CLEAN formula removes
> unprintable characters, but also removes separation between multiple words.
> Is there a way to replace unprintable characters with spaces or delimiters
> such as commas? Thanks.
> --
> Jay

Reply to this Message

CLR - 11 Dec 2007 18:26 GMT

There's a bazillion tricks to cleaning messy data. ASAP Utilities, available
at www.asap-utilities.com can be very helpful here.

Vaya con Dios,
Chuck, CABGx3

> I’m trying to fix a messy imported data set. The CLEAN formula removes
> unprintable characters, but also removes separation between multiple words.
> Is there a way to replace unprintable characters with spaces or delimiters
> such as commas? Thanks.

Reply to this Message