Saved from a previous post (I'd use the macro at the end):

Saved from a previous post.

You may want to try a macro from David McRitchie. Depending on what's in the
cell, it may work for you.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()"

If that doesn't work...

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

Then you can either fix it via a helper cell or a macro:

=substitute(a1,char(##),"")
or
=substitute(a1,char(##)," ")

Replace ## with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(##), Chr(##)) '<--What showed up in CellView?

myGoodChars = Array(" ","") '<--what's the new character, "" for nothing?

If UBound(myGoodChars) < UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

PhillyRon wrote:

Dave,

I'm getting back to the delimitor problem this morning. That Excel macro
was great: it found that it was not, in fact CONTROL-ENTER that was the
invisible characters but dec 013 and dec 010. Now how can I remove these
special characters and replace them with (for example) spaces?

BTW, I put in a few test CONTROL-ENTERs in a spreadsheet, and I tried FIND
CONTROL-J and it still couldn't find them. Could you be a bit more explicit
about what you want entered into the FIND/REPLACE box? (I know this is a bit
thick-headed, but the devil is in the details.)

PhillyRon

"Dave Peterson" wrote:

Yes it will. It just looks like it doesn't.

If you do Edit|Find
then type:
asdf(ctrl-j)qwer
you'll see that it looks weird.

If it doesn't replace those funny characters, then the users aren't using
alt-enter.

But if the edit|Find doesn't find anything, you may want to use Chip Pearson's
CellView utility to see what that character is.
http://www.cpearson.com/excel/CellView.htm

PhillyRon wrote:

Dave,

The FIND window in FIND/REPLACE won't accept CONTROL-J (the courser doesn't
move and it doesn't find anything). Is that what you wanted to search for?

PhillyRon

"Dave Peterson" wrote:

Select your range to change
Edit|replace
what: ctrl-j (same as alt-0010 on the number keypad = alt-enter)
with: whateveryouwant (or leave blank or a space character)
replace all



PhillyRon wrote:

Earl,

I tried the Text Write Program (nice macro) but apparently trying to protect
the cells with a "~" delimitor doesn't solve the problem as I thought it
would. Here's the real problem: some clients made lists within the cells,
using ALT-ENTER to change lines. This is where the parsing screws up. Is
there a way to perform a SEARCH/REPLACE to get rid of the ALT-ENTER invisible
characters?

"Earl Kiosterud" wrote:

Ron,

Try the Text Write Program at http://www.smokeylake.com/excel/text_write_program.htm
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
"PhillyRon" wrote in message
...
My Excel data contains both commas and tabs so I need to change the export
file.csv delimator to "~". How can I do this? (I tried Excel's suggestion
to go into the PC's control panel and change the txt list separator to ~ but
that didn't work.)

RonLev
Philly




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson