Shulou(Shulou.com)11/24 Report--

"Excel skateboard shoes"

There are some things I have forgotten.

But I still remember, one night,

My colleague asked me why I was so happy today.

I said that in my Excel, there is a CONCAT

Different is the most fashionable, it must be great to use!

In Xiaohua's adaptation of Excel skateboard shoes, a function is mentioned, CONCAT.

It is the "back wave" that just broke through bamboo shoots in advanced versions such as OFFICE 2019.

It wasn't until she finished writing that Xiaohua came up with a sentence to describe this "super newcomer": CONCAT saved Excel's text processing power.

After a little search, you will find that the current Excel tutorials have dabbled in it, and Xiaohua has always been unreserved.

Brain-burning ahead, please bring your own walnuts!

PS: if you think some of the formulas in this article are too difficult to learn, you can apply them.

No matter how much text is merged, it's just basic fuck! Let's first see how the function Fatty CONCATENATE completes the text concatenation. The name is so long, what is it if it is not fat? )

Two words evaluation, "chicken ribs"! No wonder the CONCATENATE function was quickly replaced by the "&" connector.

Learn from the bitter experience, crazy weight loss, fat man, CONCAT function shining debut!

Its function is to connect a list or text string area with only one parameter, that is, = CONCAT (text area)

Compared to the one-by-one concatenation of CONCATENATE functions or "&" connectors, the efficiency of CONCAT is exponential. Look at the following picture:

Xiaohua: ouch, even if you praise me like that, I won't be happy! Ha ha ha!

Fan: we are not praising you, don't YY!

Of course, in addition to merging rows and columns, multi-row and multi-column area text merging, CONCAT is also easy to do!

It is also appropriate to merge the text according to the conditions! Still using OFFICE version 2016 and below, how do you solve the problem of merging names by department?

Sort + IF method, VLOOKUP method or PQ method. Do you know these points of knowledge? )

CONCAT provides us with a new and convenient way of thinking. The formula is as follows:

{= CONCAT (IF (A2VOR A7mm D2 Magi B2JV B7 & ",", "))}

Description of the formula:

Use the IF function to judge the condition, return the name & "," for the person who meets the "department" condition, and return empty for those who do not meet the condition, thus return N "name," value.

Then use CONCAT to concatenate the text.

Mixed text to extract numbers, this is the best solution! Before the CONCAT function, the gods racked their brains to extract numbers from mixed text, especially discontiguous numbers.

What SUM+LARGE, what LOOKUP+MID, all kinds of divine nesting, all kinds of data logic.

Finally, all this was announced off the shelves after the advent of CONCAT.

CONCAT is the best solution to the problem of extracting numbers. The formula is as follows:

{= CONCAT (IFERROR (--MID ($A2 magic row), 1), "))}

Description of the formula:

Using the MID+ROW combination, each character in the mixed text is extracted separately, and then calculated using a double minus sign.

At this point, the number returns itself, and the non-numeric return error # VALUEZHING quotation IFERROR then converts the error value to null.

So the numbers in A2 are retained, the rest are replaced by empty, and CONCAT connects them to complete the extraction of numbers.

Extract the difficult problem of not repeating numbers, still win Q: Chinese is taught by math teachers, what kind of experience is it?

A: how many numbers are used in the poem "1 to 23 miles, 45 houses in Yancun"?

Identifying the numbers used in mixed text is an interesting and challenging Excel problem.

It is extremely difficult for Excel users before OFFICE 2016 to accomplish this task, and there may be a glimmer of hope with the help of PQ.

But under the iron hoof of CONCAT, there is no mountain of "text processing" that cannot be conquered!

{= CONCAT (ISNUMBER (FIND (ROW ($1 ISNUMBER)-1) (A2)), (ROW ($1) (10)-1), "))}

Description of the formula:

❶ ROW ($1 10)-1 returns the ten Arabic numerals from 0 to 9, and then uses the FIND function to find out where these numbers appear in B2.

Returns the positional order value if the A2 cell contains the number, otherwise returns # VALUE!.

❷ uses ISNUMBER to judge the return value of FIND, making the ordinal value and # VALUE! The ordered array of TRUE becomes a set of logical values, the logic being that TRUE represents the number contained in A2, and vice versa.

❸ then the IF function works, TRUE returns the corresponding number, and FALSE returns null.

❹ finishes with CONCAT, concatenating these numbers.

Seeing this, the actual combat faction cannot help but ask: this formula seems to be useless in work.

Wrong! It has a lot to do.

Actual cases: automatically calculate the list of absentees according to the department personnel list and attendance registration list.

{= CONCAT (IF (ISNUMBER (FIND (MID (SUBSTITUTE (A2, ",", REPT (", 99)), ROW ($1IF 100) * 99-98)), B2)),", ", TRIM (MID (SUBSTITUTE (A2,", ", REPT (", 99)), ROW ($1hand100) * 99-98))} ▲ to view left and right.

The formula briefly states:

We use the SUBSTITUTE function to replace the tick between names with 99 spaces, so that 99 spaces and one name form a unique set of strings, each unique string contains only one name.

Then, like extracting unrepeated numbers, we compare 99 characters as a unit and remove spaces, but the return value is swapped, including the return empty, but not the return name & ",".

Finally, use CONCAT to connect them to form an absence list.

Learn about other interesting uses! In the process of trying to accomplish the above high-energy uses with CONCAT, Xiaohua accidentally unearthed two interesting uses of CONCAT.

It is difficult to restrain the impulse to share. Don't thank me too much when you burn the last straw in the heads of your friends.

▋ fun use ❶: memorize well

Do you like the poetic and talented CONCAT by reversing the texts one by one and arranging them in reverse order?

{= CONCAT (MID (A2101-ROW ($1A2101-ROW), 1))}

Description of the formula:

The principle is simple: use 101-ROW ($1 CONCAT 100) to build a reverse array from 100 to 1, so MID starts with the last character, extracts the characters one by one, and then combines them with CONCAT.

In addition, if the second parameter of MID is greater than the length of the text, the result of MID interception itself is empty and can be connected directly.

▋ fun use ❷: discerning eyes

The various typos created by the lower limit of the children's show are definitely a nightmare for Chinese teachers and parents.

Fortunately, with CONCAT's help in correcting errors, my mother no longer has to worry about my eyes.

{= CONCAT (MID (B2Magne row ($1)) MID (A2) row ($1), MID (B2))}

▲ swipe left and right to view

Description of the formula:

Use the MID+ROW function to extract the characters from A2 and B2 respectively and compare them one by one. The error returns the corresponding characters in B2 and returns null correctly.

Finally, all these typos can be connected by the CONCAT function.

Does this article refresh your understanding of the CONCAT function?

In fact, CONCAT twin enhanced version of the function TEXTJOIN can also complete the above operation, or even better, learn more than friends might as well give it a try!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiaohua

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.