How Excel automatically converts numbers into a fixed format with numbers

Format cells – custom: as shown below.

Click OK to see the results!

Yes, right-click Format Cell Number Custom and enter Type there ("2009")"Wu""people""one""Chu""word""Section"0"No"That's it.

1. Select the cell and click "Cell" in the "Format" menu

2. Select the first item "Number" in the pop-up dialog box, and select "Custom" in the following category

3. Enter the following content in the text box on the right side of the customization and then confirm.

2009) Wu Min Yi Chu Zi Di"0"No"

0 represents the number you are about to enter in the cell, give it a try!

Right-click Format Cell Custom and enter the type there.

2009)""Wu""people""one""Chu""word""Section"0"No"

Or. If you enter 1234 in A1 and enter in B1 cell.

2009) Wu Min Yi Chu Zi Di"&a1&"No"

Cell B1 will display the required data. ok

Select the cell or a column or a row you want to enter, and right=》to format the cell.

Select "Custom" under the "Number" tab and enter it in the Type field

2009) Wu Min Yi Chu Zi Di"0"No"

You can use this to connect two cell contents. The connecting symbol is &

In the format, there seems to be a custom one, go see it, I haven't used it specifically.

Take Lenovo Xiaoxin Pad inch computer, win10 professional version and excel2010 as an example, if you need to convert the number to number format, you can right-click and select to set the cell format. Then select Customize and follow the steps to complete the setup. Here's how to set it up:

1. Open a required excel file on the computer to enter.

2. After entering the file, you can see the data in the form of scientific notation, right-click, and select Format Cells.

3. Select Custom in the format of the setting cell that appears, then enter the hashtag symbol in the type and click the OK button.

4. At this point, you can see that the data of the scientific notation has been set to a numerical format.

Sometimes in Excel processing, it is often necessary to convert the numbers in the original text format into real numbers, and the specific operation method is as follows:

<>5. Other cells use the same method, just double-click and then select Format Conversion.

Obviously, d3 and k3 are not equal, so the result will be if's non-result.

There are several possibilities for why.

1. Leave the data in column d:k unaligned.

The buttons in the red box are from left to right.

Left-aligned, centered, right-aligned, decrease the amount of indentation, increase the amount of indentation.

We will generally use the first three left-aligned, centered, and right-aligned, and here we will unalign them.

When we select Left-Align for a cell or region, the Justify Left button displays a gray macro shading, which means that the cell is left-aligned. One more click cancels the left alignment, and the shading disappears, and the color reverts to the same unshading as the other buttons.

Why deselect, the purpose is simple.

When the alignment is not selected, the numbers are on the left, the text is on the right, and the logical values are in the middle.

We might as well open an empty table, without making any settings, respectively, enter the numbers at this time, we see the stove size these numbers are right-aligned, we do not set the alignment, often we are accustomed to centering, do the setting alignment, but is not conducive to our accurate judgment of the data. Some textual data, which seems to be data, is actually text, and the text number 1 and the value 1 are of course not equal.

2. If we cancel the alignment, it is all right-aligned, which means that the comparison data is digital. Then we have to see how much he is worth.

Manually enter the values, we look in the column.

For example, if we enter this number in cell A1, cell A1 will show it.

These two numbers are more suitable. The result is, of course, false

The result calculated by the formula, we can only see the formula in the column, the eye can not see the result, at this time, we have the following methods.

Add the number of decimal places to the formula calculation cell, add a few more digits, until 0 appears in a row, and then compare it with the hand input value, so that we can see whether it is equal.

To process the data, if we hand input data are three decimal places, then we put another layer of round in the formula result k column, that is, =round (original formula, 3), at this time, it is to calculate the value of the formula, keep three decimal places, accurate to the 10,000th place, and keep the number of digits consistent with the three decimal places of hand input, and then compare.

1.Here is a pay scale, and now we need to sum the values for each column.

2.Enter the summation function at the bottom of each column of values.

3.Except for the basic salary, which results in 0 after summing, the sum results of the other columns are normal.

4.If you look closely at the data in the base salary column, you will see that there is a green triangle in the upper left corner of the cell, which indicates that the number is a textual number.

Scroll key 5You need to convert the text to numbers before you can perform the normal summation calculation, and first select the cell area of all the text values.

6.After you select it, click Data Toolbar - Columns.

7.In the pop-up column wizard dialog box, click Next without any settings.

Daqingqiao 8Then proceed to click Next.

9.Just click Done.

10.After completion, you can see that the summation result is displayed normally in the summation bar.

11.It turns out that the green triangle in the upper left corner of the cell is also missing, and the value is converted to a normal number.

12.If you don't want to change the cells of the original table, you can directly sum the textual values.

13.Write the summing formula as =sum((c2:c14)+0) and press the keyboard Ctrl+SHIF+Enter at the same time.

14.The summation result is also displayed normally even if the green triangle does not disappear.

Other methods: Use the column method (select for a column of data): select this column, the data Data Tools column, and press Finish directly;

Multi-column data: Enter 1 in a different cell, then right-click the cell, "Copy", then select the data area, right-click, "Paste Special", and select "Operation" and "Multiply".

Use the format brush.

The steps are as follows:

1.Open Excel:

2.Select the number you want to convert:

3.Press the shortcut key "Ctrl+1".", click Values:

4.Once determined, the text format is converted to a numeric format:

Enter 1 in a blank cell, and copy, select the range of cells you want to convert to number, right click, "Paste Special", "Operation" and select "Multiply".

Select the cell - right-click - format the cell - number options - select a normal or numeric value.

Point to the cell.

There is a green arrow on the top left.

Just convert it to a numerical value.

This can be done with VBA, that is, macro**.

You can use the "column" square to convert the numerical text in Duexcel to numbers in batches.

1. The feature of this format is that there is a small green triangle in the upper left corner of the single version of the metagrid. (1234 at the top is the data in normal format, 6789 is the data in text format, and 1234 at the bottom is the result of summing the data).

2. Select all the text number cells to be modified, and select the "Data" menu in the excel menu "Column".

3. The following dialog box will appear.

4. Always select Next Next Next column data format and select "General".

5. In the above method, if you need to convert the number format into a text format number, select "Text" in the last step of the operation.

In excel, you can use a formula to convert the text into numbers, and you can also use the selection dao to paste by multiplying 1 to achieve, here is a method that allows you to convert the entire column of textual values into numbers in batches by means of columns:

Tools used: Office2007

Here are some examples:

1.Convert column A text values to numbers:

2.Select column A, then select Data, Column:

3.The first two parts can be set by default, and the third step is to make sure that the column data format is "General":

4.Once completed, column A data is converted into numbers:

There are many ways to convert the amount of text numbers in Excel into numbers, and here are two simpler ways to do so.

Law. Operating version system: win10; Software rights version: Office 2007 Method 1: Direct conversion method.

Method 2: Selective paste.

1.Enter the number 1 in any cell, and copy, select the text number area, right-click, paste special:

2. In the operation, select Multiply:

3, you can turn text numbers into regular numbers: