Message-ID: <3F4A88C3.7D4EFC9@msn.com> Date: Mon, 25 Aug 2003 17:08:03 -0500 From: Dave Peterson Reply-To: ec35720@msn.com X-Mailer: Mozilla 4.78 [en] (Win98; U) X-Accept-Language: en MIME-Version: 1.0 Subject: Re: =clean(a1) References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Newsgroups: fj.comp.applications.excel,microsoft.public.excel,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.programming NNTP-Posting-Host: 1cust193.tnt2.belvidere.il.da.uu.net 67.200.45.193 Path: ccsf.homeunix.org!ccsf.homeunix.org!news1.wakwak.com!nf2.xephion.ne.jp!feed2.sphere.ad.jp!feed1.sphere.ad.jp!giga-nspixp2!newsfeed.media.kyoto-u.ac.jp!mmcatv.co.jp!news.moat.net!news-out.newsfeeds.com!propagator2-maxim!news-in.superfeed.net!cyclone.bc.net!msrtrans1!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl Lines: 1 Xref: ccsf.homeunix.org fj.comp.applications.excel:75 if it's just char(27) and char(7): =SUBSTITUTE(SUBSTITUTE(A1,CHAR(27)," "),CHAR(7)," ") And if there's a chance that those characters will be doubled up: =TRIM(SUBSTITUTE(SUBSTITUTE(A1,CHAR(27)," "),CHAR(7)," ")) "news.verizon.net" wrote: > > I want to get rid of special characters from A1. But I want it to be > replaced with space so there are 2 or 3 words: > > A1 > > TESTchar(27)TEST1char(7)TEST2 > > After clean it should look like this > > TEST TEST1 TEST2 > > Thanks. -- Dave Peterson ec35720@msn.com