How to convert numbers. Converting a number to text and back in Microsoft Excel

Numbers stored as text may produce unexpected results. Select the cells and click the button to select a conversion option. If this button is not available, follow these steps:

1. Select a column

Select the column with such cells. If you don't want to convert the entire column, you can select one or more cells. The cells must be in the same column, otherwise this process will not work. (If you have such cells in multiple columns, see the "Other conversion methods" section below.)

2. Click this button

The Columns button is typically used to separate columns, but it can also be used to convert a column of text to numbers. On the tab Data click the button Column text.

3. Click "Done"

The remaining steps in the wizard are needed to separate the text into columns. Since we only need to convert the text, click the button Ready, and Excel converts the cells.

4. Set the format

Press CTRL+1 (or +1 on Mac). Select the desired format.

Note: If you still see formulas that don't produce numeric results, you may have the option turned on. Show formulas. Open the tab Formulas and disable the option Show formulas.

Other conversion methods

Using the formula

You can use the VALUE function to return the numeric value of text.

1. Insert a new column

Insert a column next to cells that contain text. In this example, column E contains numbers that are stored as text. Column F is a new column.

2. Apply the VALUE function

In one of the cells in the new column, enter =VALUE() and provide a reference in parentheses to a cell containing numbers that are stored as text. In this example, this is the cell E23.

3. Hover your mouse

Now you need to fill the formula down. Here's how to do it: Hover your pointer over the bottom-right corner of the cell until it changes to a plus sign (+).

4. Click and drag down

Click and drag down to add the formula to other cells. You can then use the new column or copy and paste the new values ​​into the original column. Here's how to do it: Select the cells with the new formula. Press CTRL+C. Click the first cell in the source column. On the tab home click the arrow next to the button Insert and select Special insert > Values.

In programming, quite often you have to perform various transformations: encodings, data types, units of measurement, etc.

And in some areas of programming, numbers in binary representation are also quite often used. At least I have to deal with this all the time.

Quite often I created my own functions for such transformations. This is useful for development and learning.

However, with professional development there is no time to create such functions. Therefore, it is better to use ready-made ones. I will introduce you to one of these functions today.

Function BinStr

BinStr function Converts an integer to a string in binary representation. Syntax:

BinStr(Val: Longint; cnt: byte) : shortstring; function binStr(Val: int64; cnt: byte) : shortstring; function binStr(Val: qword; cnt: byte) : shortstring;

The binStr function returns a string that contains the binary representation of the integer passed through the Val parameter. The number of characters in the line will be no more than specified in the cnt parameter. That is, the line will contain only the low-order (right) bits of the number. To get a complete representation of large integers, you must specify cnt = 32. Otherwise, the most significant bits may not be included in the result.

program binstrfunc; var x, y: integer; begin x:= 100; y:= x * 245; (x, " - ", binStr(x, 4)); //0100 WriteLn(x, " - ", binStr(x, 8)); //01100100 WriteLn(y, " - ", binStr(y, 16)); //0101111110110100 z:= (Int64); WriteLn(z, ":", #10#13, binStr(z, 64)); z:=(Int64); WriteLn(z, ":", #10#13, binStr(z, 64)); ; end.

Binary number as a string

Why would you even need to output a binary number as a string?

This doesn't happen very often. But still, sometimes such a need arises. For example, if you need to track the values ​​of each bit in a number on the screen.

Quite often I pack the values ​​of logical variables into numbers, where each digit corresponds, for example, to the state of some sensor. This is necessary when you need to save space and time when transmitting data over a serial channel. Then, for example, instead of passing 32 boolean variables, I pass only 1 WORD variable, in which each bit corresponds to one boolean variable. And already in the program I decipher this number and thus obtain the states, for example, of 32 sensors.

Well, if the program is in the process of debugging and there is no time yet to display all 32 sensors on the screen, then you can simply display this number in binary form and thus understand the state of a particular sensor. This is where the BinStr function comes in handy.

Computers work with binary codes; it is more convenient for the user to deal with decimal or hexadecimal. Therefore, there is a need to convert numbers from one number system to another.

The transformation of the number X from a number system with base q to a number system with base p is carried out according to the substitution rule or the rule of division-multiplication by the base of the number system.

Substitution rule
The substitution rule is implemented based on formula (1.1) and provides for the performance of arithmetic operations with number codes in the new number system. Therefore, it is most often used to convert numbers from non-decimal to decimal.

Example .111011.011 (2) = 1 2 4 +0 2 3 +1 2 2 +0 2 1 +l 2 0 +0 2 -1 +l 2 -2 +l 2 -3 = 59.375.

Division-multiplication rule
The division-multiplication rule involves performing arithmetic operations with number codes in the original number system with base q, so it is convenient to use it to convert decimal numbers to any other positional number systems. The rules for converting whole numbers and proper fractions are different. The division rule is used to convert whole numbers, and the multiplication rule is used to convert proper fractions. To convert mixed numbers, both rules are used for the integer and fractional parts of the number, respectively.

Division rule used to convert an integer written in q-ary number system to p-ary number system. In this case, it is necessary to sequentially divide the original q-ary number and the resulting quotients by the new base p, represented in the q-ary number system. The division continues until the next quotient becomes less than p. After replacing the resulting remainders and the last quotient with digits of the p-ary number system, the code of the number is written in the second number system. In this case, the highest digit is the last quotient, and the numbers following it correspond to the remainders written in the reverse order of their receipt.

Multiplication rule is used to convert a fractional number written in the q-number system to the p-number system. In this case, it is necessary to sequentially multiply the original fraction and the fractional parts of the resulting products by the base p represented in the original q-ary number system. Integer numbers of the resulting products, replaced by digits of the p-ary number system, give a sequence of digits in the new p-ary system.

Multiplication must be performed until the required p-ary code receives the digit of the digit whose weight is less than the weight of the least significant digit of the original q-ary fraction. In this case, in the general case, the code obtained is approximately, and always with a lack of fraction value. Therefore, in the case of a reverse conversion (a p-ary fraction code to a q-ary fraction), the result may not coincide with the original value of the q-ary fraction.

Example .75,35 (10) =1001011,01011… (2) .

Convert fractions and integers from binary to hexadecimal and back is simple. It is enough to divide the binary number into groups of 4 bits ( bit we will call it a binary digit), and 4 bits begin to form directly from the point that separates the integer and fractional parts of the number. Consequently, for the integer part the groups are formed from the point from right to left, and for the fractional part from left to right. Each group has 4 bits, which is also called notebook, can be converted to one hexadecimal digit with a value from 0 to F (see Table 1.3).

Table 1.3. Matching binary tetrads and hexadecimal digits.

Binary tetrad	Hexadecimal digit	Decimal value

If there are not enough bits for the tetrad, then insignificant zeros are added to the left for the integer part, and to the right for the fractional part (see Fig. 1.3).

Fig.1.3. Convert fractional binary number to hexadecimal number.

Conversion fractions and integers from hexadecimal to binary This is also easy to do - each digit of a hexadecimal number is replaced by the corresponding binary four-bit tetrad from Table 1.3 (see Fig. 1.4).

Fig.1.4. Convert fractional hexadecimal number A18.2E 16 to binary.

Whole numbers

Conversion integer decimal numbers to binary can be done in two different ways.

1 th way. Sequential subtraction from powers of two. The largest power of two less than the number is subtracted from that number. The same operation is performed with the resulting difference until the difference becomes equal to zero (Fig. 1.5). When a number is expanded into powers of two, the binary value is obtained as follows: ones are placed in those positions that correspond to the resulting powers of two, and zeros are placed in all other positions.

Fig.1.5. Convert decimal number 197 to binary (1st method).

2 th way. Sequential division of a number by two. The quotient is written directly below the original number, and the remainder is written next to the quotient. The process is repeated until 0 remains. The binary number can be read from the remainder column down up(Fig. 1.6).

Rice. 1.6. Convert decimal number 197 to binary (2nd method).

Conversion integer decimal numbers to hexadecimal can be done by sequentially dividing a number by 16. If the result of the division is greater than 16, then the division is performed again. The last quotient obtained will be the most significant digit of the hexadecimal number. Next, the remainders from division are written down in the reverse order of their receipt (Fig. 1.7). Numbers greater than 9 but less than 16 obtained by division are replaced by the corresponding hexadecimal digits (Table 1.1 or 1.3).

Rice. 1.7. Convert decimal number 1970 to hexadecimal.

Integer binary numbers to decimal can be converted in two ways.

1 th way. Summation of powers of two for which the bits of a binary number are equal to 1:

Rice. 1.8. Converting a binary number to a decimal number (1st method).

2 th way. A binary number is written vertically, one bit per line, starting with the rightmost one. The leftmost bit is at the bottom. The decimal number is collected from bottom to top. The bottom row is 1. The number from the bottom row moves up, multiplied by 2, and is added to the bit of the current row. The result is in the top row:

Rice. 1.9. Converting a binary number to a decimal number (2nd method).

One of the common tasks that Excel users encounter is converting numerical expressions into text format and vice versa. This question often requires spending a lot of time on a solution if the user does not know a clear algorithm of actions. Let's see how you can solve both problems in different ways.

All cells in Excel have a certain format, which tells the program how to consider a particular expression. For example, even if they contain numbers, but the format is set to text, the application will treat them as plain text and will not be able to perform mathematical calculations with such data. In order for Excel to treat numbers as numbers, they must be entered into a worksheet element with a general or number format.

First, let's look at various options for solving the problem of converting numbers into text form.

Method 1: Formatting via the context menu

Most often, users format numeric expressions into text ones through the context menu.

Method 2: Tools on the Ribbon

You can also convert a number into text form using the tools on the ribbon, in particular, using the field to display the format discussed above.

The data is converted to text.

Method 3: Using a function

Another option for converting numerical data into test data in Excel is to use a special function called - TEXT. This method is suitable, first of all, if you want to transfer numbers as text into a separate column. In addition, it will save time on conversion if the volume of data is too large. After all, you must admit that clicking every cell in a range of hundreds or thousands of rows is not the best solution.

1. Place the cursor in the first element of the range in which the transformation result will be displayed. Click on the icon "Insert Function", which is located next to the formula bar.



2. Window opens Function Wizards. In category "Text" select the item "TEXT". After this, click on the button "OK".



3. The operator arguments window opens TEXT. This function has the following syntax:

   TEXT(value,format)

   The window that opens has two fields that correspond to these arguments: "Meaning" And "Format".

   In field "Meaning" you need to specify the number to be converted or a reference to the cell in which it is located. In our case, this will be a reference to the first element of the processed numeric range.

   In field "Format" you need to specify the option for displaying the result. For example, if we enter «0» , then the output text version will be displayed without decimal places, even if there were decimal places in the source. If we make "0.0", then the result will be displayed with one decimal place if "0.00", then with two, etc.

   After all the required parameters have been entered, click on the button "OK".



4. As you can see, the value of the first element of the given range was displayed in the cell that we highlighted in the first paragraph of this guide. In order to transfer other values, you need to copy the formula to adjacent sheet elements. Place the cursor in the lower right corner of the element that contains the formula. The cursor changes to a fill marker that looks like a small cross. Hold down the left mouse button and drag across empty cells parallel to the range in which the source data is located.



5. Now the entire row is filled with the required data. But that's not all. In fact, all elements of the new range contain formulas. Select this area and click on the icon "Copy", which is located in the tab "Home" on the toolbar group "Clipboard".



6. Next, if we want to save both ranges (original and converted), do not deselect the area that contains the formulas. Click on it with the right mouse button. The context action list is launched. Select a position in it "Insert Special". From the action options in the list that opens, select "Number values ​​and formats".

   

   If the user wants to replace the data in the original format, then instead of the specified action, you need to select it and insert it in the same way as indicated above.



7. In any case, text data will be inserted into the selected range. If you nevertheless choose to paste into the original area, then the cells containing formulas can be cleared. To do this, select them, right-click and select a position "Clear Contents".

Convert text to number

Now let's see how you can perform the inverse task, namely how to convert text to a number in Excel.

Method 1: Convert using the error icon

The easiest and fastest way to convert a text version is to use a special icon that reports an error. This icon looks like an exclamation mark inscribed in a diamond-shaped icon. It appears when you select cells that have the green mark in the upper left corner that we discussed earlier. This mark does not yet indicate that the data in the cell is necessarily erroneous. But numbers located in a text-type cell raise suspicions in the program that the data may have been entered incorrectly. Therefore, just in case, she marks them so that the user pays attention. But, unfortunately, Excel does not always produce such notes even when the numbers are presented in text form, so the method described below is not suitable for all cases.

If there are not one but many similar text values ​​that need to be converted, then in this case you can speed up the conversion procedure.

All array data will be converted to the specified form.

Method 2: Convert using the format window

As with converting data from numerical form to text, in Excel there is the possibility of reverse conversion through the formatting window.

After performing these actions, all values ​​of the selected range are converted into the form we need.

Method 3: Convert using Ribbon Tools

You can convert text data into numeric data using a special field on the tool ribbon.

The values ​​in the range will be converted from text to numbers.

Method 4: Applying the Formula

You can also use special formulas to convert text values ​​to numeric values. Let's look at how to do this in practice.

By the way, to convert values ​​using this method it is not at all necessary to use exclusively double multiplication by "-1". You can use any other arithmetic operation that does not change the values ​​(adding or subtracting zero, raising to the first power, etc.)

Method 5: using a special insert

The next method is very similar in principle to the previous one, with the only difference that to use it you do not need to create an additional column.

Method 6: Using the Column Text Tool

Another option in which you can convert text into numeric form is to use the tool "Text in columns". It makes sense to use it when a dot is used instead of a comma as a decimal separator, and an apostrophe is used as a place separator instead of a space. This option is perceived in English Excel as numeric, but in the Russian version of this program all values ​​that contain the above characters are perceived as text. Of course, you can process the data manually, but if there is a lot of it, it will take a significant amount of time, especially since there is the possibility of a much faster solution to the problem.

Method 7: Using Macros

If you often need to convert large areas of data from text to numeric format, then it makes sense to write a special macro for this purpose that will be used when necessary. But in order to do this, first of all, you need to enable macros and the developer panel in your version of Excel, if this has not already been done.

As you can see, there are quite a few options for converting numbers in Excel that are written in numeric form, into text format, and in the opposite direction. The choice of a particular method depends on many factors. First of all, this is the task at hand. After all, for example, you can quickly convert a text expression with foreign delimiters into a numeric one only by using the tool "Text in columns". The second factor that influences the choice of option is the volume and frequency of transformations performed. For example, if you often use such transformations, it makes sense to record a macro. And the third factor is individual user convenience.