Excel批量转文本的新函数VALUETOTEXT和ARRAYTOTEXT

  文 前 推 荐   

文本合并利器TEXTJOIN

全国地址拆分通用公式

多区域查找

如何让多表汇总在增删表后可自动更新？

编按：

两个文本方面的新函数。VALUETOTEXT，可以将引用的数据转成文本；ARRAYTOTEXT可以将区域中数据转成文本并合并到一个单元格中。

介绍两个将选定的数据转化为文本的函数：VALUETOTEXT和ARRAYTOTEXT。

1. VALUETOTEXT函数

1)作用与语法

将引用数据按常规格式效果转化为文本。

语法：

=VALUETOTEXT(引用数据,[格式])

格式有两个选项：0和1。

2）基本用法

（1）选项0转文本

格式选项0，是默认值，可以省略不写。

=VALUETOTEXT(A2:B7)

说明：

注意用色块标注的数据，转化后原来的格式都丢弃了，变成常规格式效果的文本。譬如原来的B3单元格是日期数据“2023/10/15”，转化后就变成文本数字45214。

（2）选项1转文本

=VALUETOTEXT(A2:B7,1)

说明：

相比选项0，最大不同就是除逻辑值（TRUE、FALSE）、错误值、数字外，原始数据中的文本会添加上双引号。譬如原始数据A3是文本数字1234.012，转换后变成了加双引号的文本“1234.012”。

2.   ARRAYTOTEXT函数

1)作用与语法

将选定区域中的数据按常规格式效果依据先行后列的顺序转成文本后合并到一个单元格中。

语法：

=ARRAYTOTEXT(引用数据,[格式])

格式同样有两个选项：0和1，具体含义如下。

2）基本用法

（1）用选项0合并——简洁模式

选项0是默认值，可以省略不写。这种模式不区分行列，不区分数字和文本，所以叫简洁模式。

=ARRAYTOTEXT(A19:B24)

说明：

效果等同用VALUETOTEXT转成文本后再用TEXTJOIN合并。

（2）用选项1合并——严格模式

选项1会区分行列、区分数字和文本进行合并，所以叫严格模式。它会得到常量数组。

=ARRAYTOTEXT(A19:B24,1)

说明：

合并得到常量数组。列值之间用逗号分开，行值之间用分号分开；原有的文本添加了双引号。数字、逻辑值和错误值不添加引号。

合并所得常量数组——只要不存在空值（原始数据中的空单元格转化后就是空值）——可以很方便的用等号将其转为数组。如下：

3.  综合运用

因为小窝运用面、接触面不宽，所以这两个函数的实际运用真的还没有太多思路。下面抛砖引玉，希望大家来补充。

1）按条件合并同类项目

譬如下方按组别将人员合并到一个单元格中。

=ARRAYTOTEXT(FILTER($B$2:$B$9,$A$2:$A$9=D2))

说明：

FILTER筛选出数据，再用ARRAYTOTEXT合并。

加一点难度，要求合并后日期不变：

=ARRAYTOTEXT(TEXT(FILTER($B$13:$C$20,$A$13:$A$20=E13),"yyyy/mm/d"))

2）合并到同一单元格并分行显示

公式=SUBSTITUTE(ARRAYTOTEXT(FILTER($B$25:$B$32,$A$25:$A$32=A34)),", ",CHAR(10))

说明：

由于ARRAYTOTEXT不能设置分隔符，所以合并后再用SUBSTITUTE函数将“, ”替换为换行符CHAER(10)。就这点来说，不如用TEXTJOIN来得利索。

=TEXTJOIN(CHAR(10),,FILTER($B$25:$B$32,$A$25:$A$32=A34))

课件下载方式

扫码入群，下载本文教程配套的练习文件。