Change export delimitor?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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
 
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.
 
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
 
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
 
Small correction to my note, posted below. Of course, I meant ALT-ENTER,
NOT CONTROL-ENTER.

PhillyRon

PhillyRon said:
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
Click to expand...
 
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
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
Click to expand...
 
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.
 
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
 
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 said:
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
Click to expand...
 
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)).


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
Click to expand...
 
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.
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
Click to expand...
 
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
 
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.


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
Click to expand...
 
Back
Top