arrays - Excel中的Concat Arrays单元格

Question

我有 3 个单细胞数组(没有文字，请参阅:https://support.office.com/en-us/article/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7)

A1 = {"A","B","C"}

B1 = {"1","2","3"}

C1 = {"X","Y","Z"}

我需要连接它们

D1 = "A 1 X , B 2 Y , C 3 Z"

该公式应该适用于更大的数组，而不仅仅是 3 个元素。
我不能使用VB。

谢谢你可以看看 https://www.freelancer.com/contest/Excel-Formula-1660332

Answer 1

我没有 TEXTJOIN在我的家用电脑上运行。所以当我尝试以下约束时

将有三个单元格。

每个单元格将有 3 个具有上述常量结构的条目。

然后可以使用以下很长的公式。
=""""&TRIM(SUBSTITUTE(CONCATENATE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(","&A1,",",REPT(" ",99)),"{",""),"}",""),99,99)),TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(","&B1,",",REPT(" ",99)),"{",""),"}",""),99,99)),TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(","&C1,",",REPT(" ",99)),"{",""),"}",""),99,99))," , ",TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(","&A1,",",REPT(" ",99)),"{",""),"}",""),198,99)),TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(","&B1,",",REPT(" ",99)),"{",""),"}",""),198,99)),TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(","&C1,",",REPT(" ",99)),"{",""),"}",""),198,99))," , ",TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(","&A1,",",REPT(" ",99)),"{",""),"}",""),297,99)),TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(","&B1,",",REPT(" ",99)),"{",""),"}",""),297,99)),TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(","&C1,",",REPT(" ",99)),"{",""),"}",""),297,99))),""""," "))&"""
其核心是重复使用以下提取逻辑。
TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(","&A1,",",REPT(" ",99)),"{",""),"}",""),99,99))
我的建议:如果您没有 TEXTJOIN，请使用 VBA选项。