时间戳

首页 > 解决方案 > excel公式中的智能拆分字符串 - 完整的单词

excel - excel公式中的智能拆分字符串 - 完整的单词

问题描述

如何拆分单词未拆分且结果字符串最多为 30 个字符的字符串？

input cell = 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt'

output cell = 'Lorem ipsum dolor sit amet'

我只需要公式，不需要 VBA。

标签： excelexcel-formula

解决方案

尝试这个：

Non- let 函数版本：

在此处输入图像描述

=IF(RIGHT(IF(RIGHT(LEFT(A2,31),1)=" ",LEFT(A2,30),LEFT(A2,FIND("@",SUBSTITUTE(LEFT(A2,30)," ","@",(LEN(LEFT(A2,30))-LEN(SUBSTITUTE(LEFT(A2,30)," ","")))))-1)),1)=",",
LEFT(IF(RIGHT(LEFT(A2,31),1)=" ",LEFT(A2,30),LEFT(A2,FIND("@",SUBSTITUTE(LEFT(A2,30)," ","@",(LEN(LEFT(A2,30))-LEN(SUBSTITUTE(LEFT(A2,30)," ","")))))-1)),LEN(IF(RIGHT(LEFT(A2,31),1)=" ",LEFT(A2,30),LEFT(A2,FIND("@",SUBSTITUTE(LEFT(A2,30)," ","@",(LEN(LEFT(A2,30))-LEN(SUBSTITUTE(LEFT(A2,30)," ","")))))-1)))-1),
IF(RIGHT(LEFT(A2,31),1)=" ",LEFT(A2,30),LEFT(A2,FIND("@",SUBSTITUTE(LEFT(A2,30)," ","@",(LEN(LEFT(A2,30))-LEN(SUBSTITUTE(LEFT(A2,30)," ","")))))-1)))

Office 365Let 功能版本：

=LET(a,IF(RIGHT(LEFT(A2,31),1)=" ",LEFT(A2,30),LEFT(A2,FIND("@",SUBSTITUTE(LEFT(A2,30)," ","@",(LEN(LEFT(A2,30))-LEN(SUBSTITUTE(LEFT(A2,30)," ","")))))-1)),IF(RIGHT(a,1)=",",LEFT(a,LEN(a)-1),a))

推荐阅读