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