EXCEL 内存数组合并成字符串

Question

比如内存数组 ｛"1","b","2","c","#"} 合并成 1b2c#
数组内容和长度不是固定的 不用VBA解决
或者具体问题
比如 A1中 1b2c# 在B1中 数字都加1 结果 2b3c#

数组合并的问题困扰我很长时间了，不知道是不是用函数就能解决的，求高手解答!
可能我的具体问题还误导大家了，实在抱歉 我的主要问题是数组合并的问题
并且我的意思是不用 简单的 A&B 这种的
如果用&解决对每个字符处理一次都要重写一遍函数，我举例是比较简单的，复杂的那就太麻烦了，而且有写不下的可能。不知道有没有高手有更厉害的方法啊。我感觉既然能把字符串分成数组，再组合也应该可以啊。

Answer 1

如果不用VBA，我用函数设置了一个长度小于等于20可以用的函数，当然也可以加函数把长度加的更长。根据需要了。
=IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),1,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),2,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),3,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),4,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),5,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),6,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),7,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),8,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),9,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),10,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),11,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),12,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),13,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),14,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),15,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),16,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),17,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),18,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),19,),"")&IFERROR(INDEX(IF(IFERROR(VALUE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)),"")="",RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1),RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),1)+1),20,),"") ctrl+shift+回车 一起按下

Answer 2

很遗憾，内置函数不能实现你的要求。
phonetic是合并文本的，但它不支持数组。
所以，你只有使用自定义函数吧。
自定义函数 pp
Function pp(rng)
i = Len(rng)
For j = 1 To i
a = Mid(rng, j, 1)
If a Like "[0-9]" Then
b = b & a + 1
Else
b = b & a
End If
Next
pp = b
End Function
使用时，b1输入
=pp(a1)

Answer 3

比如 A1中 1b2c# 在B1中 数字都加1 结果 2b3c#
A1=COLUMN()&"B"&COLUMN()+1&"C"&"#"
右拉

Answer 4

合并单元格
=CONCATENATE(E5,F5,G5,H5,I5,J5,K5)

Answer 5

从楼上的答案让我理解了神仙和凡人的区别
路过。。。。。。。