Knowledge Blind Spot | In those years, the Excel "Morse Code" that you didn't know how to use!

Introduction to this article

In Excel, there is such a thing (as shown in the picture). Many people can understand symbols, but they just can't use them. This may be a blind spot for many friends, I will call it the "Morse Code" !

Insert picture description here


You may use the part, but I think it is necessary as a knowledge point to tell everyone.

Introduction to common types

Looking at the picture above, you can see that there are many symbols in "Type" . What do they mean? Here I will explain it for everyone.

Insert picture description here

① G/General format

If you do not set anything, the format is the default. Meaning: What to enter and what to display.

② # sign

Meaning: If the number of digits after the decimal point is greater than the number of #, it will be rounded up according to the number of #. If the number of digits after the decimal point is less than the number of # signs, the original value will be displayed.

③ 0 symbol

Meaning: If the number of digits after the decimal point is greater than the number of 0, it will be rounded up according to the number of 0. If the number of digits after the decimal point is less than the number of 0, the insufficient digits are filled with 0.

Note: This should be compared with the use of #.

④? Symbol

Meaning: Meaning: If the number of digits after the decimal point is greater than the number of 0, it will be rounded up according to the number of 0. If the number of digits after the decimal point is less than the number of 0, the insufficient digits should be filled with spaces.

Note: This should be compared with the use of # and 0.

⑤% sign

The% sign cannot be used alone. Generally, it is used together with the above three symbols. After using the above symbols, add a percent sign at the end of the text.

Meaning: It is equivalent to multiplying the original number by 100 and adding a percent sign at the end.

⑥, symbol

The, number cannot be used alone either. Generally, it is used together with the above three symbols.

Meaning: If it appears in the middle of the number placeholder, it will be divided by an extra "," on the basis of the original number placeholder. If the "," sign is empty, the original number will be divided by 1000 on the basis of the previous display. If there are several, divide by 1000 several times.

Note: After dividing by 1000, the following decimal places will be rounded up and omitted.

Of course, these symbols can be used in conjunction with each other to create complex formats.

Small case

Say it a hundred times, it's better to use it once. Let's do a simple operation below. After you roughly know what it means, you can simply debug it in the follow-up actual study work.

Insert picture description here


Of course, there are some symbols related to "year, month, day" . The meaning here is very clear, so I won't introduce them.

supplement

In addition to the symbols introduced above, there are also a few symbols here, and I need some additional hints.

① @ symbol

The original text can be spliced ​​with any character string.

Suppose, there is such a piece of data.

Insert picture description here


I want to show this format: My name is...

Insert picture description here


Look at the final result:

Insert picture description here

②! Symbol

It can help us to display special characters, such as ",", which is a special character in a custom format. But we want to display it, what should we do?

Similarly, there is such a piece of data.

Insert picture description here


How to add a "," after each character?

Insert picture description here


The final effect is as follows:

Insert picture description here


Finally, add another one: ";;;" Such a three-group number can help us hide cells, so I won't talk about it here.