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

Hello, Hi, I am the uncle Mr Zhao, who prefers function formulas and likes to use Excel charts to manage warehouses.

In our work, we always encounter all kinds of text processing problems, such as digital format conversion, text merging and splitting and so on.

Faced with these common and troublesome problems, Excel has professional text functions to solve, such as the TEXT function, which most cousins have used.

In addition, Excel is also constantly updating and optimizing TEXT functions, such as the text merge function TEXTJOIN and the text split function TEXTSPLIT.

Next, let's look at the specific usage of these text functions through three cases.

The TEXT function converts text format the TEXT function converts numbers into the text format you want.

As shown in the following picture, in order to facilitate the distribution of materials, the company needs to subtotal the quantity of each department, and the unit will be distributed according to the department after conversion. The effect is shown in column F.

(the unit conversion relationship is: 6 boxes per box, 10 yuan per box. )

Enter the formula in cell F3:

= TEXT (INT (E3gray 60), "# boxes") & SUBSTITUTE (TEXT (MOD (E3Power60) / 10, "# boxes # / 10 boxes"), "/ 10 boxes")

The above formula uses the TEXT function twice.

The TEXT function formats the number through the second parameter format code, thus changing the way the number is displayed. Its usage is: = TEXT (number, format code)

The format code of the TEXT function is similar to the cell custom format code. It is divided into four conditional sections, separated by half-width semicolons: [condition 1]; [condition 2]; [does not meet condition 1 and condition 2]; [text]

By default: positive; negative; zero; text

👉 Formula Analysis:

❶, let's go back to the formula in the case:

TEXT (INT (E3amp 60), "# cases;")

First, INT (E3amp 60) gets the number of boxes, that is:

INT (393x60) = 6.

Then use the TEXT function to display the value through the format code "# box;;":

If it is a positive number, how many boxes will be displayed, if it is 0, it will not be displayed. 6 is a positive number, and the result shows 6 cases.

❷ TEXT (MOD (E3pm 60) / 10, "# boxes # yuan / 10 boxes;")

MOD (E3pd60) means that excluding the number of boxes, there are still 33 yuan left. Divided by 10 into how many decimal boxes, the result is 3.3.

Then the TEXT function converts this value into a fractional format with 10 as the denominator (for example, 3 / 3 / 10), and gets 3 boxes of 3 / 10 boxes.

Then replace the extra text "/ 10 boxes" with empty characters with the SUBSTITUTE function.

Finally, ❸ concatenates TEXT (INT (E3pyramid 60), "# box;") and TEXT (MOD (E3Power60) / 10, "# box # block / 10 box;;") with the conjunction symbol "&" to get the final unit conversion result.

TEXTJOIN function text merge as shown in the following figure, according to the name of the E2 cell, column B contains all the characteristics of the name, merged into the right F2 cell, separated by a tick sign (,).

At this point, we can directly use the TEXTJOIN function to finish the work.

This function is a new text merge function in Office 2016. With this function, cell text merging becomes easy and easy.

Enter the following formula in cell F2:

= TEXTJOIN (",", TRUE,IF (B2VOR B16mm E2MAG C2RO C16,))

👉 Formula Analysis:

The basic usage of the TEXTJOIN function is: TEXTJOIN (spacer, whether to ignore whitespace, what to merge)

The 1st argument of the ❶ TEXTJOIN function uses the dot sign as the delimiter

Parameter 2 of ❷ uses TRUE, which means to ignore empty text

The third parameter IF of ❸ (B2IF B16 parameter E2), uses the IF function to determine whether the B2:B16 is equal to the name of the E2 cell, and if so, returns the corresponding characteristics in the C2:C16, otherwise returns empty text

❹ finally uses the TEXTJOIN function to ignore the empty text merging characters in it and get the desired result.

How can the TEXTSPLIT function text split be directly converted into volume according to the specifications of column B? The effect is shown in column C:

At this point, you can easily split the values in the specification with the TEXTSPLIT function, and then calculate them.

Enter a formula in cell C2

= PRODUCT (--TEXTSPLIT (B2, "*"))

👉 Formula Analysis:

❶ first uses the TEXTSPLIT function to split the B2 string into a column with an asterisk.

The TEXTSPLIT function splits strings based on delimiters.

TEXTSPLIT (text, column delimiter, [row delimiter], [ignore null], [fill text])

The last three parameters are omitted in this example.

❷ then converts the split array into a numeric value with "- -"

❸ finally uses the PRODUCT function to multiply and get the volume.

Little TIPS:

TEXTSPLIT function, which can only be used by partners who install Office365 and join the preview experience program.

Written at the end of the ❶ TEXT function is a very frequently used text function, which can set various text styles of numbers according to the format code. If you don't think you've learned enough, click the text link below to read the previous article:

What on earth is the "plastic surgery master" in Excel skr? Level up immediately after learning to look good!

Grind skr people's long formula, the routine is so simple?!

The ❷ TEXTJOIN function can concatenate the characters of the region into strings according to the specified rules, which solves the problem of merging text according to conditions.

The ❸ TEXTSPLIT function, which splits strings according to specific delimiters, is a powerful text-splitting function.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhao Jiaoyang, Editor: Zhu Lan

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.