Menu
homeBreakdowns

How to change numbers in Excel. Changing column names from numeric to alphabetic

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 your worksheet and right-click on cell A1. From the context menu that appears, select the “Paste Special” option. In the dialog box, select Unicode Text 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 pasting special, 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

The Windows Notepad program does not require the use of complex settings and functions, but only acts as an intermediary in copying and preliminary preparation of 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 the regional settings of the 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 that has different system regional settings, you may experience problems with the calculations.

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 plate onto a blank sheet of paper, and then prepare it for performing calculations and calculations.

An important disadvantage of this method is the complexity of its implementation if some fractional numbers with a dot after 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.

Good afternoon, dear reader!

In this article, I want to look at such a powerful tool as Find and Replace in Excel. This is a very powerful and multifunctional tool that will allow you to quickly and efficiently find the necessary information in worksheets and, if necessary, replace the text with the desired one.

To call the “Find and Replace” window, you need to select a certain one in which the values ​​will be selected (in the case where there is no selection of a range, the search will take place on the entire worksheet). Then in the control panel, the “Home” tab in the “Editing” block, click the icon with the “Find and Select” drop-down menu and the “Find” and “Replace” options become available. This option is also available by pressing the combination Ctrl + F. There are two tabs available in the “Find and Replace” window: “Find”, if you are looking for some information, and “Replace”, used in cases where the text requires replacement. In addition, you can use the advanced search functionality by clicking on the “Options” icon.

Looking for in the document

In the “Find and Replace” window, in the “Find” tab, write down the search argument and click the “Find all” icon, this is a regular search.
For a more detailed and broad search you need to:

  • In the “Search” drop-down menu, write down the search location: a worksheet or an entire book;
  • In the “View” menu, indicate which search option you need: by columns or by rows;
  • We set in the “Search area” line in which places to search: in values, notes or formulas;
  • Using the “Entire cell” or “Consider case” items, you indicate the need to take into account the case of search characters, and whether the contents in the area will be searched in fragments or in full.

When you click the “Find all” icon, a list will be displayed with the specified addresses of all cells that were found. And by clicking the “Find Next” icon, a search will occur with the selection of one segment in one click, the next click will show the next argument.

Replacing found information

To replace an argument with the required one, open the “Find and Replace” window in Excel and select the “Replace” tab. In the “Find” field, enter the value that requires replacement, and in the “Replace” field, write down what you actually need to change it to. If desired or necessary, you can change the parameters with advanced functionality, which I described above.
For an accurate and detailed replacement, you need to click the “Find Next” icon, then Excel selects the first match and then, if a replacement is needed, click “Replace”; if not, click “Find Next” again. If you are sure of replacing the values, then simply click on “Replace All”, the system will only give you a message about the number of replacements made. In the event that a replacement occurs, you can cancel the action using the quick access panel by clicking the “Undo” icon or simply by using the hotkey combination Ctrl + Z.

Search by data formats

In addition to data and any other symbols, Excel can also find all cells using the specified formats and, as a result, replace the found format with the required one.

For example, there was a need to replace all values ​​in italic text with bold text. To do this, you need to go to the “Replace” tab, enable advanced functionality with the “Options” button and in the “Format” settings specify the search parameters and the replacement parameter.
When you click on the “Format” button, a new “Find Format” dialog box will open and go to the “Font” tab in the “Typeface” setting, select “Italic”.
These operations must be done twice, the first time to find and the second time to replace. The “Format” buttons are placed side by side, one above the other.
Now, when you click the “Replace All” button, the formats will be changed from italics to bold.

Tricks and examples of using Find and Replace capabilities inExcel

I use my own application experience as an example. Very often I analyze the traffic to my articles to improve their behavioral factors and readability. To do this, I upload a report from Yandex.Metrica that looks like this (articles are selected randomly):
As you can see, the service also attaches the name of the site to the title of the article, but I don’t need this at all for analysis. Therefore, part of the line “ | Clear Excel" I will find and delete with replacement, substituting only the space " " for the replacement.
Now click on the “Replace All” icon and see a dialog about the replacements made. That's it, everything is done as it should be! In addition to the built-in search and replace capabilities in the Excel control panel, it is also possible to perform these operations using

It is known that in the normal state, column headings in Excel are designated by letters of the Latin alphabet. 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 the standard method. 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.

Today we will talk about how to change numbers to letters in Excel. In some cases this is necessary. However, many users of the Excel editor are accustomed to the fact that row numbers are indicated by numbers, while columns can be identified by letters.

Instructions

In order to solve the question of how to change numbers to letters in Excel, first of all we make some changes to the style of links. To do this, go to the table editor settings. As a result, the numbers in the columns will be replaced by letters. Note that the value of the described setting is saved in the file along with the table, thus, when opening the material, in the future we will load the specified parameter. Excel will instantly recognize previously made edits and adjust the column numbering to the user's requirements. If we open a file that has a different value for this setting, we will see a different style in the column numbering. In other words, if you come across a table with unusual settings, they will remain the same in all other materials.

Excel Options

We move on to the next stage of solving the question of how to change numbers to letters in Excel, and open the main menu of the editor by clicking on the large round button in the upper left corner of the window. Further down we will see two functions. One of them is called "Excel Options". Click on it. The described actions can be performed without using the mouse - the main menu opens by first pressing the ALT key and then “F”. In turn, the letter “M” will allow us to access the Excel parameters we need.

Link style

In order to solve the question of how to change numbers to letters in Excel, select the “Formulas” item. It is located on the left in the settings window that opens. We look for the parameter that is responsible for working with formulas. It is the first item in this section, marked “Link Style,” that determines how columns will be designated on all pages of the editor. In order to replace numbers with letters, remove the corresponding checkbox from the described field. This type of manipulation can be performed using both a mouse and a keyboard. In the latter case, use the keyboard shortcuts ALT + 1. After all the steps taken, press the “OK” button. This way the changes made to the settings will be recorded. In earlier versions of this software, the button to access the main menu has a different appearance. If you are using the “2003” edition of the editor, use the “Options” section in the menu. Next, go to the “General” tab and change the “R1C1” setting. So we figured out how to change numbers to letters in Excel.

If you were sent a file, and instead of letters in the columns there are numbers, and the cells in the formulas are specified by a strange combination of numbers and the letters R and C, it’s easy to fix it, because this is a special feature - the R1C1 reference style in Excel. It happens that it is installed automatically. This can be corrected in the settings. How and why is it useful? Read below.

R1C1 reference style in Excel. When numbers appeared in columns instead of letters

If instead of the names of the columns (A, B, C, D...) numbers appear (1, 2, 3...), see the first picture - this can also cause confusion for an experienced user. This most often happens when a file is sent to you by mail. The format is automatically set as R1C1, from R ow=string, C olumn=column

The so-called R1C1 link style is convenient for programming in VBA, i.e. for writing.

How can I change it back to letters? Go to the menu - Upper left corner - Excel Options - Formulas - section Working with formulas - Link style R1C1 - uncheck the box.

For Excel 2003 Tools - Options - General tab - Link style R1C1.

How can we use R1C1?

For understanding, let's give an example

The second convenient option is to write down the address depending on the cell in which we write it. Add square brackets to the formula

As we can see, the formula cell is 2 rows and 1 column behind the record cell

Agree, this opportunity may come in handy.

What is it for?

As we said earlier, the format is convenient for programming. For example, when writing the addition of two cells in code, you may not see the table itself; it will be more convenient to write the formula by row/column number or behind this cell (see example above).

If your table is so huge that the number of columns exceeds 100, then it will be more convenient for you to see the column number 131 than the letters EA, as it seems to me.

If you get used to this type of links, then finding an error when you turn on the view mode will be much easier and more clear.