Hi, I am an uncle who prefers function formulas and likes to use Excel charts to manage warehouses, Mr. Zhao~
In our work, we often encounter data operations with units, such asSum data with units, extract values in different units, and combine quantities in different unitsetc.
Advertisement
Let's discuss with you how to deal with these data with units through 5 typical cases.
1. Sum of numbers with units
As shown below, sum the amounts in column C.
❶ First use the SUBSTITUTE function to replace the character “element” in column C with a null character:
=SUBSTITUTE(C3:C8"元""")
Advertisement
❷ The column of numbers generated at this time is still in text type and cannot be summed directly. Two negative signs need to be added in front to convert it into a numerical value:
=--SUBSTITUTE(C3:C8"元""")
❸ Finally, you can use the SUM function to sum the converted values to get the final result:
=SUM(--SUBSTITUTE(C3:C8,"元",""))
2. Extract values in different units
As shown below, extract values from the text with different units in column B to column C:
❶ First use ROW ($1:9) to generate a set of sequence values starting from 1 and increasing:
=ROW($1:9)
❷ Then use this set of sequence values as the second parameter of the LEFT function, expand and intercept the characters in the current text in column B one by one from left to right, and generate a column of text array:
=LEFT(B3,ROW($1:9))
❸ Finally, use the LOOKUP function to find the last value in this array and return the result:
3. Split text with multiple units
As shown below, split numbers with multiple units from column C to columns D:F:
❶ First, use the LEFT function to extract the characters in front of the unit from the text in the current column C:
=LEFT($C3,FIND(D$2$C3)-1)
❷ Then use the RIGHT function to expand the number of extracted characters one by one from right to left, intercept the characters, and generate an array:
=RIGHT(LEFT($C3,FIND(D$2, $C3)-1)ROW($1:9))
❸ Then use the LOOKUP function to find the last value in this array, and then fill in the copy formula:
=-LOOKUP( 1, -RIGHT( LEFT($C3, FIND(D$2, $C3) - 1), ROW($1:9) ))
❹ Finally, use the IFNA function to mask the error value (#N/A) in the formula and return the result:
=IFNA( -LOOKUP( 1, -RIGHT( LEFT($C3, FIND(D$2, $C3) - 1), ROW($1:9) ) ), "")
4. Consolidation of quantities from different units
As shown in the figure below, the values in columns C:E and the corresponding units are combined into the effect of column F:
❶ First, use the TEXT function to format the value in the current line. If it is a positive number, the characters consisting of the value and the unit will be displayed. Otherwise, a null character will be returned to generate a line of text array.
=TEXT(C3:E3,0&C$2:E$2&"")
❷ Then use the CONCAT function to combine this line of text arrays into a string of characters to get the result:
=CONCAT(TEXT(C3:E3,0&C$2:E$2&""))
5. Unit conversion
As shown in the figure below, according to the box size of column D, the quantity in column C is converted into the effect of column E:
❶ First divide the current quantity by the box gauge, and then use the INT function to round to get the number of boxes:
=INT(C4/D4)
❷ Then the TEXT function sets the text format for the box number. If it is a positive number, the value is connected to the unit, otherwise a null character is returned.
=TEXT(INT(C4/D4)"0 箱")
❸ Then use the MOD function to find the remainder based on the current quantity and box specifications to get the remaining number of pieces:
=TEXT(MOD(C4,D4),"0 件;;")
❸ Finally, connect the two formulas together with the connector “&” to return the final result:
=TEXT(INT(C4 / D4), "0 箱;;") & TEXT(MOD(C4, D4), "0 件;;")
Okay, I’ll share it here today.