Excel CSV encoding. Select text encoding when opening and saving files. How to add symbols-hieroglyphs in the text

Often, working with various files in the Excel program, the user may encounter the fact that instead of clear letters there will be an incomprehensible font that is impossible to read. WINDOW ALL THE INCORRECT CODING. In this article we will talk about how to change the encoding in Excel so that the words could be read.

Method First: With NotePad ++

It so happened that to change the text encoding is easier in a third-party program, rather than in the "Excele". That is why we will now consider the way in Excel to change the encoding using the NotePad ++ program.

So, to correctly execute all actions, follow the instructions:

Run the Notpad ++ program.
Click on the "File" button.
In the menu that appears, select "Open" (also can be done by pressing the Ctrl + O key combination).
In the Explorer window that appears, route the path to the desired file, the encoding in which is incorrect, and click the Open button.
In the file that opens, click the "Encoding" button, which is located above the toolbar.
In the menu, select the "Convert to UTF-8" item, since it is this encoding that the program "Excel" perceives with a bang.
Click the "Save" button, which is located on the left side of the toolbar, and close the program window.

That's all, now you know how to change the encoding in Excel in one way. Although it is the fastest, convenient and simple, but there are still others who cannot be silent.

The second method: Using the "Text Wizard"

Directly in the Microsoft Excel program itself there are also tools that will change the encoding. It is about that and now we are talking about, or rather, about the "master of texts":

Open the Excel program. Please note that it needs to be launched by a non-double click on a file that is incorrectly displayed, but directly the program with an empty sheet.
Go to the "Data" tab.
Click the "External Data" button, and select "From Text" in the drop-down menu.
In the Explorer window that appears, specify the path to the file that has encoded problems, and click the "Import" button.
Now the "Text Wizard" itself opens directly. It needs to change the file format, which is why click on the same name and select from there "Unicode (UTF-8)".
Click "Next".
The next window also skip by clicking the "Next" button, all settings in it must be default.
Now you need to determine the file column data format and in accordance with it, select one of the items of the same name. According to the final, click Finish.

After all, you must specify the very first cell so that the data is fit at the top of the table.

How to change the encoding in Excel in the second way, understandable. Go to the third.

Third way: saving

How to change the encoding in Excel? To implement the third method, it is necessary to make a number of the following actions:

Open the file in the program.
Click "File".
Select "Save As".
In the menu that appears, select the extension and click "Service", from the drop-down menu - "web document parameters".
In the window that appears, go to the "Encoding" tab and select it from the "Save Document as" list.
Click "OK".

Now it remains only to specify the folder where the file will be saved. With the subsequent opening, the text should be displayed correctly.

If the table instead of the necessary information you saw questions, hieroglyphs or other incomprehensible signs, it means you need to figure out how to change the encoding in Excel. This feature is available in the Microsoft Office itself. But you can get rid of the problem with other methods.

Sometimes we get incomprehensible hieroglyphs

The computer perceives any information as a set of numbers. Therefore, each printed sign has its own code. It consists of numbers and letters of the Latin alphabet. The characters and the corresponding numbers them are encoding.

She has several standards. For example, Koi-8, Unicode, Windows-1251, ASCII. And if the file with the text is saved, say, in Koi-8, in Excel it will be incorrectly displayed in Unicode. Since in these sets, different codes are assigned to the same symbols.

How to change the encoding in the program?

To work with the table in which the standard is used, not specified by default in the program, you need to change the encoding. There are several ways.

With NotePad

If it does not turn out to turn "Krakoyar" to normal text, open the file in the NOTEPAD ++ program. It applies for free. Configure there displaying characters, and then continue to work in Excel.

Open the file in the NOTEPAD ++ program

Create a backup of the document. Or save information from it to some other table.
Run NotePad.
Navigate to the file - Open (File Open) and specify the path to the table. In the "File Type" field, place "All Types" ("All Types"). Or specify a specific format. Tables have extensions. CVS, .xls, .xLTM, Xlam, .xlm. In Notpad, the text will be displayed without a grid. No need to edit anything in it.
On the right below in the status bar will be depicted by the standard used now.
Open the Encoding menu. It is at the top of the window.
Click "Convert to UTF-8" ("Convert to UTF-8"). The document will be converted to the desired format. This Excel standard perceives normally and will not turn into a random flow of strange characters.
Now select which signs must be used. In the same menu encoding, hover the drop-down list. He is there one.
For Russian language, select Cyrillic - Windows-1251. If the text, let's say, on Arabic or Greek - mark the appropriate set of characters. Different countries use different standards.
The program will ask to confirm the action.
If it does not help, try other Cyrillic fonts.
It is also necessary to save the text in Exeline format.

Open the file in UTF-8

Via the program interface

Here's how to change the encoding in Excel using the built-in features:

Run the program. No need to open the completed document. Need a "clean" sheet.
Go to the "Data" tab in the menu bar.
On the "Receive External Data" panel, click "From Text".
In the File Type list list (it is located next to the "Cancel" button, select "All" or "Any". So the Excel formats will be displayed in the window, and not only .Txt.
Set the path to the table.
A master of imports will open.
In the "Format" field, you can select the desired standard.
In the Preview area, it is shown how text will look like with a set of symbols. You can scroll through the list and search for which setting is suitable.
When you select the desired option, click Finish.

You can use built-in features

How to save the file in a specific encoding?

It can be done so that in the file you saved the definite Excel encoding.

Click on the green button "File" (in Office 2007 you need to click on the logo on the left at the top).
Parameters - additionally.
Scroll down to the "General" section.
Button "Web Document Settings".
Coding tab.
In the "Save Document as" field, specify the desired value and click OK.

You can still do this:

Click on the file or office logo.
Select "Save As ...".
Click on "Service". There will be the same "web document parameters".

How to add new encoding to office programs?

If none of the standards approached your document, add new ones.

Start - control panel.
"Programs and Components" or "Deleting Programs".
Click on Office or Excel (depending on whether you have the entire product package microsoft office or only Excel).
"Change" button. It over the main workspace.
Put the marker to "add or delete" and click "Continue".
Open the branch shared tools - multilingual support.
Click on the black arrow next to the desired standard and turn on the "Run on My Computer" function.
Click "Continue" and wait for the end of the process. New sets will be added.

In Excel, you can change the encoding if the characters are displayed incorrectly. This feature is available in the program toolkit. But sometimes a third-party software is used.

With the need to change text encoding, users who work browsers, text editors and processors are often encountered. However, when working in the Table Excel processor, such a need can also occur, because this program processes not only the numbers, but also the text. Let's figure it out how to change the encoding in Excele.

Text Encoding - This set of electronic digital expressions that are converted to those understandable to the user characters. There are many types of encoding, each of which has its own rules and language. The skill of the program to recognize a specific language and translate it to those understandable for an ordinary person signs (letters, numbers, other characters) determines whether the application will be able to work with a specific text or not. Among popular text encodings should be allocated as follows:

Windows-1251;
Koi-8;
ASCII;
ANSI;
UKS-2;
UTF-8 (Unicode).

The last name is the most common among encodings in the world, as it is considered a kind of universal standard.

Most often, the program itself recognizes the encoding and automatically switches to it, but in some cases the user needs to specify its appearance. Only then can it work correctly with coded symbols.

Excel has the greatest number of encoding problems with the Excel program when you try to open CSV files or export TXT files. Often, instead of the usual letters when you open these files through Excel, we can observe incomprehensible characters, the so-called "Crakozyabry". In these cases, the user needs to make certain manipulations in order for the program to start correctly display data. There are several ways to solve this problem.

Method 1: Change the encoding using NotePad ++

Unfortunately, a full-fledged tool that would allow you to quickly change the encoding in any type of texts from Excel. Therefore, it is necessary to use multi-step solutions for these purposes or resort to third-party help. One of the most reliable ways is to use the NotePad ++ text editor.

Despite the fact that this method is based on the use of third-party software, it is one of the easiest options for transcoding the contents of the files for Excel.

Method 2: Application of text wizard

In addition, you can make the conversion and using the built-in program tools, namely the text wizards. Oddly enough, the use of this tool is somewhat more complicated than the use of a third-party program described in the previous method.

Method 3: Saving a file in a specific encoding

There is also a reverse situation when the file must not be opened with correct data display, and save in the installed encoding. In Excel, you can perform this task.

The document will be saved on a hard disk or removable media in the encoding you have identified. But you need to consider that now always documents stored in Excel will be saved in this encoding. In order to change it, you have to go out again. "Web document parameters" and change settings.

There is another way to change the encoding settings of the saved text.

In fact, the question is not so trivial, as it may seem at first glance. CSV format, as follows from its name, implies the use of a comma () symbol as a separator. However, many programs and services are allowed by using other characters. These include MS Excel, especially if we are talking about the Russified version. In this article, I want to solve the problem with the opening of the export CSV file from Google tool for webmasters in MS Excel. However, the topic is relevant for other options.

The problem of exporting search queries (PZ) from Google Webmaster

Problem with encoding
Comma separator in CSV
What helped

Let's summarize

The problem of exporting PZ from Google Webmaster

Many of us use Google tool for webmasters Only to add a site. It is wrong, there is a lot of useful information here, for example, a list of search queries for which users find and enter the site. See the report: Search Traffic\u003e Search queries - After selecting the desired site in the list.

Here there are also data exports in CSV format, with the ability to open it in Google Tables (online analog Excel). Look for the " Download this table" By the way, the number of displaced requests on the page does not affect the completeness of the data exported.

The problem is that the CSV file uses the comma separator and the UTF-8 encoding. So even in Google Tables, instead of Cyrillic, you will receive only a set of questions.

In other words, we need to change the encoding on ANSI. And to open the CSV file to Excel, replace the semicolute (,) separator on the decimal point symbol (;).

CSV with a comma separator in UTF-8

Problem with encoding

The easiest to decide codion problem. To do this, you can use any text editor with this feature, for example NotePad ++. You can download it for free on the official website: unotepad-plus-plus.org.. Next, run the editor and open our CSV file in it, and then click the menu item " Coding"And change to the desired, i.e. item " Convert to ANSI».

I have a reverse process in my picture: transformations from ANSI in UTF-8 - but, I think you are noticeable, and the principle is the same.

Comma separator in CSV

Now that concerns separate separator. In fact, it is not necessary to change something for Google tables. And in the English version of MS Excel, it can roll, check. Nevertheless, if you need a replacement, then it should be considered here that it is easy to replace the comma () on the symbol of point with a comma (;) will not work, because The file can be the values \u200b\u200bcontaining this symbol. They are usually placed in quotes. For example:

value, 1, "value, with a comma",

In theory, it should help configure the Windows operating system itself: Start\u003e Control Panel\u003e Language and Regional Standards. On the "Formats" tab, click the " Change this format ..."In the window that opens, the" Numbers "tab replace" List elements separator»To the desired, i.e. The symbol of the point with a comma (;) is changed to the comma (,).

True, it did not work for me. Yes, and change the operating system settings, as for me, it's not gracious things. Nevertheless, I decided to specify this solution to solve the problem, because Most specialized forums refer to it.

What helped

Alas, but some additional file processing with extension. CSV in MS Excel I have not found. However, there is a possibility of processing, but only if you change the file extension, for example, by .txt. So change the file extension and open it to Excel.

The "Master of Text (Import)" window appears. Here you can select the source data format, the starting line of import and file encoding (We need 1251: Cyrillic). By the way, why was it necessary to change the encoding when it can be chosen in the wizard? Because the UTF-8 encoding, at least I do not have. Click button " Further».

In the next step, we can choose a separator symbol, and several. Put a tick " comma"And clean the tick" tabulation sign" Here you can choose and the string limiter symbol. This is the one that is used for values \u200b\u200bin which special (double quotes, comma, point with comma, new row, etc.). A variant with a double quotation, in my case, this is what is needed. Note the "Sample Data Sample" field, the data has become a table. Click button " Further».

In the last step, you can specify the column data format, though the selection is not large here: general (default), text, date and skip columns. The last option seems to me the most interesting, because Allows you to immediately exclude unnecessary columns. Simply select the appropriate column and click the option " skip column" Click button " Ready».

Excel will form the table we need, where we can set the desired column width and the format of the cells, but this is a completely different story. I will only note that there is a problem with recognizing the same interest.

Online service to normalize CSV files

However, all the above problems decides the simple online service Normalization CSV. It allows you to change encoding and separator symbol. True there is a restriction in 64 000 byte (how much is it in KB?) For a downloaded file, but CSV files usually weigh not very much, it should be enough. The result of processing is displayed as a regular text file, it can be saved with the extension. CSV.

By the way, the script converts percent value, for example: 95% - to the desired value for the "interest" column in Excel, i.e. It is divided by 100 and we obtain a floating stamps, for example: 0.95. If you need some more transformations: write, try to modify.

Well, if the proposed online service does not suit you, you can always use the above instruction.

Let's summarize

Some online services provide the ability to export data into a simple and convenient CSV format. As it follows from the name, it implies the use of a comma (,) as a separator. However, many applications interpret this format somewhat quitually, which leads to natural problems. In addition, the UTF-8 encoding is increasingly popular, while Excel or Google tables use ANSI.

Solve the encoding problem will help, for example, NotePad ++, and in order to deal with the separator, it is enough to change the extension of the file by .Txt and use the text master in Excel. However, all these tasks are able to solve free online service Normalization CSV.True it is worth considering its restriction on the volume of the downloaded file. I have everything on this. Thanks for attention. Good luck!

at 19:44.

Edit Message

16 comments

Files and documents created on the computer always have their own encoding. It often happens that when sharing files or download them from the Internet encoding in which they are created, not read by our computer. The reasons for this can be different - as the absence of the necessary encoding in the program, which we want to open the file, and simply the absence of some program components (an additional package of fonts, for example).

Let us consider how to change the encoding of a unreadable file or document in different programs.

Change encoding on the browser page

For Google Chrome

Select the menu "Settings" → "Tools".
The string "encoding" - we bring mouse, and a list of possible encodings appears in the browser.
Choose for Russian sites "Windows 1251". If it does not help, try "automatically".

For Opera

Click Opera → "Settings"
On the left menu "Websites" → Field 2 "Display" → "Set up fonts"
In the "Coding" field, choose "Cyrillic (Windows 1251)".

For Firefox

Firefox → Settings → Content.
Opposite the default font string, press the "Advanced" button.
At the bottom of the window, select "Cyrillic" coding & rarr (Windows 1251).

Change encoding in Word

Consider the encoding change procedure using the WORD 2010 example

Open the document.
Tab "File" → "Parameters".
We choose the string "additionally". In the "General" section opposite the string "Confirm the file format conversion at the opening", we put a tick. Click OK.
Next, the "File Conversion" window opens. Select "Coded Text", click OK.
Next, in the window that opens, mark "Other" and select the encoding from the list, which will display the necessary text. In the Sample window, you can view how text is displayed in a particular encoding selected by us.

If the procedure has passed, it is not helped to display the document, you can try to change the font. Sometimes the document can be displayed as "squares" or other characters if there is no appropriate font in the program.

Change encoding in Excel

Consider the procedure for changing the encoding for Excel 93-2004 and 2007:

Open a unreadable document using the NotePad ++ program.
Select the Encoding menu → Convert to UTF-8.
Symbols will not change, only encoding at the bottom of the screen will change. Next, select a set of characters. If this is Russian: Encoding → Character Sets → Cyrillic → Windows-1251.
Click "Save". Open the file in Excel. If the text is not read, we try to repeat steps 3-4.

Change text encoding

Open the file in a standard notebook text processor.
Click "Save As".
In the saving window that opens, select the place where we want to save the file, the type of document - text, and also set another type of encoding.
Save.
We try to open the document.

Additionally, read the article

VKontakte Facebook Odnoklassniki

With the transition to all new versions of Windows, the problem of the existence of a set of codings of the Russian language almost sued