How to return letters in excel. Finding and replacing data in Excel - Find and Replace command

All tips

Excel is one of the most powerful and effective tools created by Microsoft Corporation. software allows the user to use specialized tools when working with text and tables, allowing them to quickly find the necessary elements that subsequently need to be changed. The application's tools help automatic mode view several sheets of a document or even the entire workbook. When working in Excel, the user does not need to manually search for the required piece of text in order to subsequently replace it.

Sometimes users need to replace the same data throughout an entire document

To activate a dialog box that allows you to find and change, for example, a comma to a period, you first need to designate a range of cells. A direct search will be performed in the selected fragment. The user should know that if a single cell is selected, the program tool will check the entire sheet for a match with the entered element. To take advantage of the features of the Excel program and make a replacement (for example, change a comma to a period), in the “Home” section you need to go to the “Editing” subcategory and click “Find and Select”. To open the dialog box, press together Ctrl keys and F.

The window open in front of the user allows you to use the following commands: “Find” (search for a text fragment) and “Replace”. The “Options” button will help you switch to advanced file viewing mode.

Search tools

So, if the user needs to identify a piece of text in a document, for example, a period or a comma, it must be entered into the search field. Next, you need to specify the parameters according to which the symbol will be detected.

First of all, you need to specify a location that can be set in the “Search” category; depending on the specified range, the search can be performed either on a specific sheet or in the entire book.

Among other things, in the “View” section, you can tell the application tool to view the document exclusively by rows or columns.

The viewing area can be specified in formulas, values, or notes.

Scroll various features so wide that the system can search for information in a case-sensitive manner and view the entire contents of a particular cell or some fragment.

The user must be prepared for the fact that the entire specified range of cells will be viewed through the search, and the program will sequentially find and highlight each match. You can view everything found step by step, moving through the cells with the desired content in one click. “Find All” will allow you to display a list in the document that contains the addresses of absolutely all matches.

Replacement Tools

IN Excel program It’s enough to simply replace one character with another (change a comma for a period); to do this, open the replacement section in the “Find and Replace” category. By searching, enter a comma (a symbol or element that will subsequently be changed) into the window. In the “Replace” section, enter a period or a letter, meaning the text that will appear in place of the changed one.

The symbols will change after clicking on the “Next” button. The software tools will show the user the cells in which substitution will need to be made. With a click, Excel can go through the entire document, highlighting each match step by step. If in some place there is no need to change the symbols (comma to period), you need to skip the replacement by simply clicking on “Next”. If the user does not need to check every match, you can use the “Replace All” button. Cancel action will help

It is known that in normal condition Column headings in Excel are indicated by Latin letters. But, at one point, the user may discover that the columns are now indicated by numbers. This can happen for several reasons: various kinds of malfunctions in the program, one’s own unintentional actions, intentional switching of the display by another user, etc. But, whatever the reasons, when such a situation arises, the issue of returning the display of column names to the standard state becomes relevant. Let's find out how to change numbers to letters in Excel.

There are two options for bringing the coordinate panel to its usual form. One of them is carried out through the Excel interface, and the second involves entering a command manually using code. Let's take a closer look at both methods.

Method 1: using the program interface

The easiest way to change the display of column names from numbers to letters is to use the program’s direct tools.

Now the names of the columns in the coordinates panel will take the form we are used to, that is, they will be indicated by letters.

Method 2: Using a Macro

The second option involves using a macro as a solution to the problem.

After these actions, the alphabetic display of the names of the sheet columns will return, replacing the numeric option.

As you can see, an unexpected change in the name of column coordinates from alphabetic to numeric should not confuse the user. Everything can be very easily returned to its previous state by changing the Excel settings. It makes sense to use the option using a macro only if for some reason you cannot use in a standard way. For example, due to some kind of failure. You can, of course, use this option for experimental purposes, just to see how this type of switching works in practice.

A period instead of a comma for fractional numbers can have significant consequences when calculating in Excel. Most often, such errors occur when data is imported into a table from other sources.

If fractional numbers have a dot instead of a comma, the program automatically treats them as a text data type. Therefore, you should format and prepare your imported data before performing math and calculations.

How to replace a period with a comma in Excel?

Select and copy the data from the table below:

Now go to the worksheet and click right click mouse over cell A1. From the appeared context menu Select the Paste Special option. In the dialog box, select "Text in Unicode encoding» and click OK.

As you can see, Excel recognizes numbers only in column C. The values in this column are aligned to the right. In other columns to the left. In all cells the default format is “General”, and in cells D3, D5, E3, E5 the “Date” format is generally displayed. We copied the data via paste special and all formats of the original table were eliminated. There is only one reason - instead of a comma there is a period. This data type is not prepared and cannot be used for calculations.

Note. If you copy data from other sources without special insert, then the format is copied along with the data. In such cases, the “General” (default) cell format can be changed. Then it is impossible to visually distinguish where the number is recognized and where the text is.

All subsequent actions must be performed from scratch. Delete everything on the sheet or open a new one for further work.

To replace a period with a comma in imported data, you can use 4 methods:

Method 1 replacing a period with a comma in Excel using Notepad

Program Windows Notepad does not require the use of complex settings and functions, but only acts as an intermediary in copying and preliminary preparation data.

The Notepad program replaced all periods with commas. The data is now ready to be copied and pasted into the worksheet.

This is a very simple but very effective method.

Method 2 temporarily change Excel settings

Before changing a period to a comma in Excel, properly evaluate the task at hand. It may be better to make the program temporarily treat the period as a tenth separator in fractional numbers. We just specify in the settings that in fractional numbers the separator is a dot instead of a comma.

To do this, open “File” - “Options” - “Advanced”. In the “Editing Options” section, you should temporarily uncheck the “Use system delimiters” checkbox. And in the “Integer and fractional separator” field, remove the comma and enter a period.

After performing calculations, it is strongly recommended to return the settings to default.

Attention! This method will work if you make all the changes before importing the data, and not after.

Method 3: Temporarily change Windows system settings

The principle of this method is similar to the previous one. Only here we change similar settings in Windows. In regional settings operating system you need to replace the comma with a period. Now let's learn more about how to do this.

Open “Start” - “Control Panel” - “Regional and Language Options”. Click on the “Advanced” button. In the window that appears, change the first field “Integer and fractional separator” - enter the value we need. Then OK and OK.

Attention! If you open this file on another computer where other system parameters regional standards, problems with calculations may occur.

Method 4: Use the Find and Replace function in Excel.

This method is similar to the first. Only here we use the same function from Notepad, but in Excel itself.

In this method, unlike the above, we first paste the copied sign onto Blank sheet, and then we prepare it to perform calculations and calculations.

An important disadvantage this method is the complexity of its implementation, if some fractional numbers with a period after the insertion were recognized as a date and not as text. So we'll get rid of the dates first, and then we'll deal with the text and dots.

All periods have changed to commas. And the text was automatically converted into a number.

Instead of the 4th and 5th points, you can use a formula with functions in a separate column:

For example, select the cell range G2:I5, enter this formula and press CTRL+Enter. And then move the values of the cells in the range G2:I5 to the range D2:F5.

This formula finds a point in text using the FIND function. Then the second function changes it to a comma. And the VALUE function converts the result into a number.

Once, an acquaintance approached me with a request to help set up Excel, namely, change the numbers in the columns to letters. In general, an .xsls file was transferred from a flash drive to his computer, which later had to be edited and everything calculated the numbers you need using formulas. But the first surprise was that when opening the document, instead of the usual letters in the column name there were numbers, but without attaching any significance to this, he continued to work further.

When it came time to enter formulas for calculations, nothing worked. Excel did not want to calculate anything, and when entering formulas, the numbers that were needed were put in. By clicking on column No. 5 in the formula, the number “–7” was displayed, after which a message was displayed stating that the “-” sign cannot be used in formulas. Having contacted me to help return it to its former form, so that everything would be as before, I began to look for how to change numbers to letters, but I never found the “Change column headers” item in the settings.

After tinkering with the settings, I decided to reinstall Excel; this did not bring any results, although perhaps the reason was that I did not clear the Aplication Data\Microsoft folder in the user profile. Disappointed that the reinstallation did not help, I returned to studying the settings in detail. After some time, settings that change the column names were found. And in order to spend a few minutes on all this, look below where they are.

Changing column names from numbers to letters in Excel 2003 and 2007-2013.

To change in Microsoft Office 2003 we need, having opened an office, go to top panel window and clicking " Service" choose " Options"Then in the window that opens, go to the tab " Are common" It is in this menu that we uncheck the item “ R1C1 link style».

In Excel from 2007 to 2013, the menu was slightly changed, but the principle of replacing numbers with letters was not affected. In general, by clicking on " File» –> « Options» –> « Formulas" and going to the parameters for working with formulas, also uncheck the " R1C1 link style».

In fact, everything is quite simple and can be done in a few clicks.

For those who like to enter manually or perhaps for some reason you cannot click the menu items discussed above, I will give an example of how this can be done by specifying the desired command.

To return to digital form we type the following: Application.ReferenceStyle=xlR1C1. Which option to use is your choice. If something doesn’t work out, leave your questions in the comments and don’t forget

There are situations when you need to replace one character (or group of characters) with another in a document. There can be many reasons, ranging from a banal error to reworking the template or removing spaces. Let's find out how to quickly replace characters in a program Microsoft Excel.

Of course, the most in a simple way replacing one character with another is manual editing cells. But, as practice shows, this method is not always the easiest in large-scale tables, where the number of similar characters that need to be changed can be very large. large quantity. Even searching for the right cells can take a significant amount of time, not to mention the time spent editing each of them.

Fortunately, Excel has a Find and Replace tool that will help you quickly find the cells you need and replace characters in them.

Search and replace

Simple search replacement involves replacing one sequential and fixed set of characters (numbers, words, signs, etc.) with another after these characters are found using a special built-in program tool.

You can find all results that match your query at once.

Automatic replacement

Can be done automatic replacement by pressing just one button. To do this, after entering the values to be replaced and the values to be replaced, press the button "Replace All".

The procedure is completed almost instantly.

The advantages of this method are speed and convenience. The main disadvantage is that you must be sure that the characters entered need to be replaced in all cells. If in the previous methods it was possible to find and select the necessary cells to change, then when using this option such a possibility is excluded.

Extra options

In addition, there is the possibility of advanced search and replace using additional parameters.

While in the “Replace” tab, in the “Find and Replace” window, click on the Options button.

A window opens additional parameters. It is almost identical to the advanced search window. The only difference is the presence of a settings block "Replaced by".

The entire lower part of the window is responsible for searching for data that should be replaced. Here you can set where to search (on a sheet or in the entire workbook) and how to search (by rows or columns). Unlike a regular search, a replacement search can be done exclusively using formulas, that is, using the values that are indicated in the formula bar when selecting a cell. In addition, right there, by checking or unchecking the boxes, you can specify whether to take into account the case of letters when searching, or whether to search for an exact match in the cells.

You can also specify which format cells will be searched for. To do this, click on the “Format” button opposite the “Find” option.

After this, a window will open in which you can specify the format of the cells to search.

The only value setting for insertion will be the same cell format. To select the format of the inserted value, click on the button of the same name opposite the “Replace with...” option.

The exact same window opens as in the previous case. Here you can set how the cells will be formatted after replacing their data. You can set alignment, number formats, cell color, borders, etc.

Also, by clicking on the corresponding item from the drop-down list under the button "Format", you can set a format identical to any selected cell on the sheet, you just need to select it.

An additional search limiter can be an indication of the range of cells among which the search and replacement will be performed. To do this, simply select the desired range manually.
Don’t forget to enter the appropriate values in the “Find” and “Replace with...” fields. When all the settings are specified, select the method of performing the procedure. Either click on the “Replace All” button, and the replacement occurs automatically, according to the entered data, or click on the “Find All” button, and separately replace in each cell according to the algorithm described above.

As we see, Microsoft program Excel provides quite a functional and handy tool to search and replace data in tables. If you need to replace absolutely all values of the same type with a specific expression, then this can be done by pressing just one button. If the selection needs to be made in more detail, then this possibility is fully provided in this table processor.