Office Hacks: Enter box numbers better in Excel

In the process of using Excel to create information tables, we sometimes have to enter employee ID cards, bank cards and other information, in order to facilitate viewing and checking, usually need to add boxes to fill in. However, these ID numbers are long, if you just use the traditional cell as a box, it is easy to cause the upper and lower rows of data format disorder. With the help of Excel’s “paste picture link”, you can more easily achieve the effect of box filling. The following Excle 2019 as an example, how to create a filled box for the 18-digit ID number.


Open the original file and create a new worksheet, and then the employee name and corresponding ID card data are entered into the new worksheet in columns A and B (note that the ID card data to use text format). Then fill in the C1:T1 1:18 data, and adjust the cell to the appropriate size, where each cell corresponds to a number in the ID card number (Figure 1).


Figure 1 Cell settings

Positioned to C2 enter the formula “= MID (Sheet2!$B2,Sheet2!C$1,1)”, and then to the right, down to fill, so that each employee ID data will be filled in turn to the C:T column of cells (Figure 2).

Formula explanation.

MID (Sheet2!$B2,Sheet2!C$1,1), that is, the use of MID function to $B2 (note the absolute reference to the column) to extract the figures, Sheet2!C$1,1: that the data displayed in C2 is in accordance with the number of bits displayed in C1 as the starting position, “1” said intercepted from left to right B2 1 character, here note that C$1 said the absolute reference to the line, so that the left pull to fill the formula, in D2: T2 will be filled in turn from B2 extracted figures to the specified cell.


Figure 2 Filling data

Select C2: T2 data, right-click and select “Copy”, then locate the original data table where the ID data cell, right-click and select “Paste → Selective Paste → Paste Picture Link”, so that the above ID data with the box will be linked to the link The picture will be inserted in the form of a link, adjust the size and position of the picture as prompted to let the ID card numbers with the box display. position to the picture, you can see in the formula bar similar to “=Sheet2!$C$2:$T$2” formula, this means that the picture content linked to C2:T2 data, when the original data change the data displayed on the picture will also be changed simultaneously (Figure 3).


Figure 3 Insert link image

Because of the need to replace all ID data in the document using pictures, here you can simply record a macro to quickly generate a link to each employee’s ID card picture. Start the recording of the macro, the same as above to perform the operation of copy and paste as a picture link, then open the macro editing window, follow the prompts to change which “C2:T2” (such as to “C3:T3” is the fourth Lee data, U2 to U3 is the fourth Lee data paste as a picture to U3, other similar). The implementation of this macro can be quickly extracted to all staff picture links (Figure 4).


Figure 4 Macro Code

Leave a Comment