Prev: Cancel Read Only
Next: Formula to locate all jobs in Month then break up into categories
From: Sagar on 22 Mar 2010 05:40
I am trying to convert excel file to Tab delimated text file.But if there is
any comma in between any string then "" is automaticaly appearing in
converted text file.
E.g In my Excel file I have sting like

Good,Morning

In text file this is getting displayed as

"Good.Morning"

Can you please suggest me how to avoid " at start and end of string.

From: Dave Peterson on 22 Mar 2010 08:17
If the data doesn't use any double quotes, then I'd just open the text file in
my favorite text editor (like NotePad) and replace them with nothing.

On the other hand, if the data contains those double quotes, I'd use a macro:

(saved from a previous post)

Here are three sites that you could steal some code from:

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(or maybe you could build your own formula and copy|paste into Notepad.)

Check out Earl's Text Write program first. It may do exactly what you want
right out of the box.

=========
Another way is to use a helper column and concatenate each of the fields the way
you want:

=a1&"|"&b1&"|"&text(c1,"dd-mm-yyyy")&"|"&text(d1,"0000000.000")
(where | isn't used in your data)

then drag down.

Select that column. Copy it. Paste it into Notepad and save it as a plain old
text file there.

And change the | to the tab character using your favorite text editor. I'm not
sure if NotePad supports this kind of change.


Sagar wrote:
>
> I am trying to convert excel file to Tab delimated text file.But if there is
> any comma in between any string then "" is automaticaly appearing in
> converted text file.
> E.g In my Excel file I have sting like
>
> Good,Morning
>
> In text file this is getting displayed as
>
> "Good.Morning"
>
> Can you please suggest me how to avoid " at start and end of string.

--

Dave Peterson
 | 
Pages: 1
Prev: Cancel Read Only
Next: Formula to locate all jobs in Month then break up into categories