teach you to make Excel's name display more aesthetically flexible technology

After school, the Excel student roster, which is submitted electronically by grade, I found that there are some problems, the class teacher reported that the names of the students have spaces in the word, and the location of the space is uncertain, the number of spaces is also uncertain, which makes the name of this column of data is very untidy, uncoordinated. It can be time-consuming and laborious to adjust one by one. How to solve the problem of the name of the hollow lattice? After many attempts, I finally found the answer.

First, delete the space in the name

There are two ways to delete a space in a name:

Method One: Substitution method. Select the column A that contains the name, perform edit → replace, enter a space in the Find what field, leave nothing in the Replace with column, click Replace All, and make sure you exit.

Method Two: Function Formula method. The "substitude ()" function in Excel can help us easily remove the spaces in our names. In the B2 cell, enter the formula "=substitute (A2," "," ")", and then make a copy of the formula using the fill handle.

Note: There is a space between the first quotation mark in the formula, and the second quotation mark has no space.

Second, let the name right and left aligned

The student's name uses the word, some three characters, some two Chinese characters, the printing is very beautiful, wants the student name to use the word is two words with three word right and left alignment also has two kinds of methods:

Method One: Format set method. Select the name cell that we have removed the space, and then click format → cell to select the Distribute alignment option in the horizontal alignment in the Open Cell Format dialog box to make sure that the student's name is aligned around the word after exiting.

Method Two: Function Formula method. Use the combination of "IF", "LEN" and "MID" three functions in Excel to align student names around the word. The specific example is: Enter the formula in cell C3: "=if (B2) >=3,b2, (MID (b2,1,1) &&" &&mid (b2,2,1)), and make sure that the formula is copied using the fill handle.

After the above methods, the students ' names become much more beautiful.