Some friends complain that the function concatenate of the linked text in Excel is not convenient, and it is not easy to link the text in one area together. But there are some hidden functions in office that often bring us surprises, such as a hidden function in Excel that phonetic the text together easily.
Note: Introduction to CONCATENATE functions Scope of application: Microsoft Office SharePoint Server 2007, Windows SharePoint Services 3.0 Function: Merges multiple text strings into a single text string. Syntax: Concatenate (text1,text2,...) TEXT1, Text2,... 1 to 30 text items that will be merged into a single text item. These text items can be text strings, numbers, or column references.
|
The Help file for the phonetic function is interpreted as "This function only applies to Japanese version," and it should not appear in Chinese Excel. But in fact, it always exists, but no one has used it, because this function is used to extract the phonetic characters from the Japanese text string.
A chance, I found this function can be used in Chinese version of Excel. In the D1 cell input formula =phonetic (A1:C4), the result is exactly the "merge area character" We dreamed of. When you use this function in Chinese Excel, the description in the Help file (if reference is a range of cells, returns the Furigana text string in the upper-left cell of the range.) If reference is an area of nonadjacent cells, returning the error value #n/a is "misleading".
Microsoft online Help:
Phonetic function Scope of application: Microsoft Office Excel 2003 Function: Extracts phonetic (furigana) characters in a text string. This function applies only to Japanese versions. Syntax: PHONETIC (Reference) Reference is a text string or a reference to a single cell or a range of cells that contains a furigana text string. Description If reference is a range of cells, the Furigana text string in the upper-left cell of the range is returned. If reference is an area of nonadjacent cells, the error value is returned #N/A. |
The following is an instance of this function. Compare whether the data in Table II is completely duplicated from the table data. In the J2 cell, enter the formula =sum (PHONETIC (OFFSET ($A $ $D $2,row ($1:$5)) =phonetic (F3:I3)) to end the entry by the Triple key (*1), copying the formula down, If table two data is completely duplicated from table one, the formula result will be greater than or equal to 1, or 0.
Further testing found that this function ignores the logical values, error values, values, and formulas in the zone reference. This feature makes it easy to distinguish between text and formulas that result in text. , cell B10 Input formula =if (Istext (A10) * (PHONETIC (A10) = ""), "formula", "text", cell text is displayed as "text", the formula is displayed as "formula."