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

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

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

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,

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

If it doesn't find anything, then it sounds as if your spurious characters
aren't CHAR(0010). Look at =CODE(MID(A1,NNN,1)), where NNN would be 10 if
you're trying to identify the 10th character in cell A1.
--
David Biddulph

"PhillyRon" wrote in message
...
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

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,

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

Small correction to my note, posted below. Of course, I meant ALT-ENTER,
NOT CONTROL-ENTER.

PhillyRon

"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

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

Did you hit and hold the control key when you hit the j key?

Or did you type the characters "ctrl-j"?

It's never failed for me.

PhillyRon wrote:

Small correction to my note, posted below. Of course, I meant ALT-ENTER,
NOT CONTROL-ENTER.

PhillyRon

"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

Dave,

Both the substitute function (for a single cell) and the CleanEmUp macro
work sweetly. Thanks so much. Just in case I'm out in the field without the
macro, is there a way to do a SEARCH/REPLACE using the asci## that CellView
displays?

PhillyRon

"Dave Peterson" wrote:

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

Yes, select the range in question, assume you have html trash where one of
the most common is char(160), so press ctrl + h, place cursor in the find
what box and hold down
alt and press 0160 on the numpad keys, then release the alt key, leave
replace with blank or with a choice of your fancy, and click replace all.
I realize that if you're on the road you are using a laptop but the numpad
keys are available albeit somewhat a nuisance to activate on laptops. On
many laptops you press the Fn key to activate them. Or you can prepare a
list with these characters on your desktop in Excel and use that list on the
road, basically a 2 column table with the char number in the left column and
the char itself in the right column, then just select the cell with the
particular char you want to replace and press F2, then shift home to select
it and then ctrl + c to copy it, make sure you haven't selected anything
from the list when you do the replace and then just paste the char into the
find what box


--
Regards,

Peo Sjoblom




"PhillyRon" wrote in message
...
Dave,

Both the substitute function (for a single cell) and the CleanEmUp macro
work sweetly. Thanks so much. Just in case I'm out in the field without
the
macro, is there a way to do a SEARCH/REPLACE using the asci## that
CellView
displays?

PhillyRon

"Dave Peterson" wrote:

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

Just to add to Peo's reply...

You can use:
=code(mid(a1,x,1))
to find the code for the character at position X in A1.
=code(mid(a1,3,1))
will find that code for position 3.

=substitute() will work all the time--or at least I've never seen it fail.

But I have seen edit|Replace with alt-#### fail with different codes (like 0013
(carriage return)).



PhillyRon wrote:

Dave,

Both the substitute function (for a single cell) and the CleanEmUp macro
work sweetly. Thanks so much. Just in case I'm out in the field without the
macro, is there a way to do a SEARCH/REPLACE using the asci## that CellView
displays?

PhillyRon

"Dave Peterson" wrote:

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


--

Dave Peterson

Ps.

Put a copy of CellView and your workbook with the macro on a thumbdrive and take
it with you. Open them just like any other file. When you close excel, the
owner of the other pc won't be the wiser.

PhillyRon wrote:

Dave,

Both the substitute function (for a single cell) and the CleanEmUp macro
work sweetly. Thanks so much. Just in case I'm out in the field without the
macro, is there a way to do a SEARCH/REPLACE using the asci## that CellView
displays?

PhillyRon

"Dave Peterson" wrote:

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


--

Dave Peterson

Dave and Peo,

I want to thank both of you guys for some excellent workthroughs on this
delimitor problem. Before I put this thread to sleep, just one final
question. I discovered why the FIND/REPLACE operation was not working for
some of your previous solutions: the invisible characters cannot be erased
by backspacing in the FIND slot, they just cumulate and cannot be removed
except by shutting down and restarting Excel. Is there some way of erasing
these ALT#### characters to prevent this?

PhillyRon

"Dave Peterson" wrote:

Ps.

Put a copy of CellView and your workbook with the macro on a thumbdrive and take
it with you. Open them just like any other file. When you close excel, the
owner of the other pc won't be the wiser.

PhillyRon wrote:

Dave,

Both the substitute function (for a single cell) and the CleanEmUp macro
work sweetly. Thanks so much. Just in case I'm out in the field without the
macro, is there a way to do a SEARCH/REPLACE using the asci## that CellView
displays?

PhillyRon

"Dave Peterson" wrote:

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


--

Dave Peterson

When I open the Edit|Find dialog, all the text in the find box is selected. I
can just hit the delete key to erase the contents of that textbox.

When I open the edit|replace box, the Replace textbox is selected and I can use
the same technique.

I can also double click on either textbox and all the text is selected. Then
hit the delete key.

You'll be able to test it easier without using the ctrl-j keystroke.



PhillyRon wrote:

Dave and Peo,

I want to thank both of you guys for some excellent workthroughs on this
delimitor problem. Before I put this thread to sleep, just one final
question. I discovered why the FIND/REPLACE operation was not working for
some of your previous solutions: the invisible characters cannot be erased
by backspacing in the FIND slot, they just cumulate and cannot be removed
except by shutting down and restarting Excel. Is there some way of erasing
these ALT#### characters to prevent this?

PhillyRon

"Dave Peterson" wrote:

Ps.

Put a copy of CellView and your workbook with the macro on a thumbdrive and take
it with you. Open them just like any other file. When you close excel, the
owner of the other pc won't be the wiser.

PhillyRon wrote:

Dave,

Both the substitute function (for a single cell) and the CleanEmUp macro
work sweetly. Thanks so much. Just in case I'm out in the field without the
macro, is there a way to do a SEARCH/REPLACE using the asci## that CellView
displays?

PhillyRon

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson