How Excel can automatically populate with functions to circumvent special numbers

Excel's automatic padding is very efficient, but sometimes we don't want to see some numbers, like when we build floors, membership cards, and so on, many people don't want to see numbers like 4 and 7. So how to avoid it?

In fact, this problem can be solved well by cleverly using the nesting of substitute functions. Let's make a concrete statement by actual operation. Our husband is a list with no 4 and 7.

First open a blank Excel table and fill in the number 1 in the A1 cell.

Next, locate the cursor to the A2 cell and enter the following formula in the formula bar:

=substitute (Substitute (a1+1,4,5), 7,8)

Then drag down the black square fill handle in the lower-right corner of the A2 cell, what do you see? Is it the only 4 and 7 of these two numbers in all the data generated?

So how does this magical substitute function exclude the two digits of 4 and 7? In fact, as long as the basic parameters of the function to grasp the meaning of all understand.

In the formula nested by the two substitute functions in "=substitute (Substitute (a1+1,4,5), 7,8)", the inside one-layer function "substitute (a1+1,4,5)" Skips over the cell with the number 4 filled to 5, and the outer layer function is filtered by the first function, and then the number 7 is filtered and replaced with 8. Therefore, after double filtering, whether 4 or 7 in a cell is replaced by a subsequent number specified in the function 5 or 8, 4 and 7 do not appear again.

If explained again, the function of "Substitute (a1+1,4,5)" is to determine whether the automatically populated data in a1+1 (i.e. A2) cells is 4, and if so, replace it with 5. This is the meaning of the three parameters that appear successively in the function.

So, if you're going to expand your mind, what if you don't want to have another piece of data in the number string (like 2 don't want to appear)? Smart you must have guessed it--wrap it outside. Substitute function, described as "=substitute (substitute a1+ 1,4,5), 7,8)), 2,3 "on it."

Learn this simple example, if you open a shop as a boss, want to give your membership card, but know that everyone does not like with 4 and 7 of the number, then how to automatically generate without 4 and 7 of the "Lucky card" number? Suit, just on the basis of the above example, modify the starting membership card number, For example, set a 2016123890 membership card starting number, followed by subsequent data filling, so that the resulting membership cards are "lucky members".

The above example takes advantage of the substitution function of the substitute function for characters in the text, which, in fact, has a finer 4th option that specifies the number of occurrences of the character to be replaced instead of replacing it all. Take a look at the syntax description of the function:

Substitute (Text,old_text,new_text,instance_num)

The parameter meanings are as follows:

Text: A reference to a character that needs to be replaced, or to a cell that contains text.

Old_text: For old text that needs to be replaced.

New_text: The text used to replace Old_text.

Instance_num: A numeric value that specifies the old_text to replace the first occurrence of a new_text. If Instance_num is specified, only old_text that meet the requirements are replaced, otherwise all old_text appearing in text will be replaced with new_text.

From this we can see that in the above two examples, we actually use only the first three parameters of the function, and the 4th parameter omitted is not used. Assuming that in the above case, we specify that the number of the first occurrence of 4 or 7 can not be, the rest can be, then, just add a parameter at the end of the function can be. Note separated by a half-width comma.

These are just the tip of the iceberg of the substitute function application, and more use depends on how flexibly you apply it to other functions or formulas in practical applications.