mxqchina应助中学到的-Excel2016新函数textjoin应用

mxqchina

如何将一个单元格内的数字倒置？先谢谢各位老师了！！！（已解决）
http://club.excelhome.net/thread-1269017-1-1.html
(出处: ExcelHome技术论坛)

此贴中，坛友哭泣的数字需要讲形如123454545这样的数据变为545454321这样，如下是2楼给出数组公式：

1. =TEXT(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1)),REPT(0,LEN(A1)))

复制代码

excel2016在今天上线后，增加了新的函数TEXTJOIN，超级强大，在也可以不用vba了，且支持数组。

看看新版公式缩短多少，且支持文本型，长度也轻松突破15位。

=TEXTJOIN(,TRUE,MID(A1,100-ROW($1:$99),1))


TEXTJOIN系统帮助如下，供参考。

TEXTJOIN functionThis article describes the formula syntax and usage of TEXTJOIN, one of the text functions in Microsoft Excel.
Important: This function is available if:

• You are an Office 365 subscriber and have the latest version of Office installed on your PC.
• You are using Excel Online.
• You are using Excel Mobile.
• You are using Excel for Android phones and tablets.
  

DescriptionThe TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
SyntaxTEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
[td]

argument	Description
delimiter                (required)	A text string, either empty, or one or more characters enclosed by double quotes, or a reference to a valid text string.  If a number is supplied, it will be treated as text.
ignore_empty                  (required)	If TRUE, ignores empty cells.
text1                (required)	Text item to be joined.  A text string, or array of strings, such as a range of cells.
[text2, ...]                (optional)	Additional text items to be joined.  There can be a maximum of 252 text arguments for the text items, including text1. Each can be a text string, or array of strings, such as a range of cells.

For example, =TEXTJOIN(" ",TRUE, "The", "sun", "will", "come", "up", "tomorrow.") will return The sun will come up tomorrow.
Remarks

• If the resulting string exceeds 32767 characters (cell limit), TEXTJOIN returns the  #VALUE! error.
  

ExamplesCopy the example data in each of the following tables, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

mxqchina

再增加一个类似vba函数里的split函数就好了。

流浪铁匠

textjoin解决了文本型内存数组合并的函数难点
但这题用另一个concat会更简洁