如何从Excel中的字符串中提取特殊字符前的文本？

I have below text on column A1:

我在A1栏下面的文字:

100-This_Is_My-Test_200

Looking to get output as below in column B1:

希望在B1列中获得如下输出:

100-This_Is_My-Test

so basically it needs to remove text starting from last _ (underscore)

所以基本上它需要从上一个_(下划线)开始删除文本

I tried below:

我试过以下:

=TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)))  

Output: 200

=TRIM(LEFT(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)))  

Output: 100-This

1 个解决方案

#1

=LEFT(A1,FIND("}}}",SUBSTITUTE(A1,"_","}}}",LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))))-1)