Save as Text with Unicode and Tab Delimited

Alejandro Rodriguez

New Member
Joined
Jul 19, 2005
Messages
40
Hello,

In my excel file I have chinese and some other special characters and many cells have text with commas.

My problem starts when I try to save my file as a Text.
When I Save As with the Unicode option I can see the chinese characters in my text file but also I see a lot of quotes """ because I have commas in my xls file.
When I Save As with Tab Delimited option to solve the comma problem, the chinese characters become ????

It seems that cannot be possible to do it manually cause there is no option to Save As with Unicode and Tab option together.

So I would deeply appreciate someone could give me a solution with VBA code to save my file as Text with Unicode and Tab Delimited option.

thanks in advance for your kind help.
regards,

PS. I searched the forum but I haven't found a situation similar to mine.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
If you want to eliminate """ then can't you just replace """ with nothing via Text editor ?

Am I missing something ?
 
Upvote 0
thank you jindon for your reply.

if I replace the quotations with nothing then I will have more fields (columns) in my text file (this because of the information with commas in the cells of my excel file)

for example my excel file is like this
in row 1 are my headers
a1:char b1:pinyin c1:description
-------------------------------------------------
a2:我们 b2:wǒmen c2:we
a3:没有 b3:méiyǒu c3:not have, there is not, be without
a4:家子 b4:jiāzi c4:household, family

in this example I have 3 columns.
when saving as a text file with unicode option the registers of column C are displayed with quotations because those cells have commas.
if I eliminate the quotations my text file will show as it would have more columns because of the commas.

I tried to solve this problem not taking the commas as a delimiter but tabulation, so when i save my text file with tab delimited option i have the registers in the 3 coulmns that i need but the chinese characters becomes ????.

as far a i know, in my case saving as text with tab delimited option works better, but my big problem is that chinese characters become all in questions marks ????.

it would be great to have an option to save with unicode and tab delimited option together.

i will appreciate your help.
thanks again,
 
Upvote 0
Then try
Code:
Sub test()
Dim i As Long, txt As String
With ActiveSheet.UsedRange
    For i = 1 To .Rows.Count
        txt = txt & vbCrLf & Join$(Application.Transpose(Application.Transpose(.Rows(i).Value)), vbTab)
    Next
End With
 
Open Replace(ThisWorkbook.Name, ".xls", ".txt") For Output As #1
    Print #1, Mid$(txt, Len(vbCrLf) + 1)
Close #1
End Sub
 
Upvote 0
thank you jindon,

the code runs but i can't see where my new text file is saved.

i checked in my folder file and also in unit C:\ but i can't find the file created.
may i know where is it saved?
 
Upvote 0
Hi

It should have saved the file down in the same folder as the workbook is in (with a .txt extension instead of .xls). If you are not finding it here, then use this small modification of Jindon's code:

Code:
Sub test()
Dim i As Long, txt As String
With ActiveSheet.UsedRange
    For i = 1 To .Rows.Count
        txt = txt & vbCrLf & Join$(Application.Transpose(Application.Transpose(.Rows(i).Value)), vbTab)
    Next
End With
 
Open Replace(ThisWorkbook.FullName, ".xls", ".txt") For Output As #1
    Print #1, Mid$(txt, Len(vbCrLf) + 1)
Close #1
End Sub
 
Upvote 0
Richard thank you,

yes, now i can find it.

but the new txt file still shows me the chinese characters as questions marks besides some special characters become just as normal letters. like this:

char pinyin description
-------------------------------------------------
?? women we
?? méiyou not have, there is not, be without
?? jiazi household, family


it should be like this:

char pinyin description
-------------------------------------------------
我们 wǒ men we
没有 méi yǒu not have, there is not, be without
家子 jiā zi household, family



how to solve that?

appreciate your help,
 
Last edited:
Upvote 0
I think you need to use the FileSystemObject if you want to manipulate text files in unicode (I'm not totally certain about this). I will have a dig around in one of my old posts that deals with this. It may take me a couple of hours to come back to you.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top