我想在excel the公式中使用,该公式返回值:
Original Text Desired Result
7. Hello.abc Hello
7.1 Hello.abc Hello
11. Hello mary.bab Hello mary
12.1. Hello.bab (hi there) Hello
12.2.4.a. Hello hi.abc Hello hi
Hello.no Hello
我已经尝试了该公式,该公式返回2点之间的值:
=SUBSTITUTE(MID(SUBSTITUTE(". " & A1&REPT(" ";6);".";REPT(";";255));2*255;255);";";"")
但是,如果原始文本为 12.1. Hello.bab (hi there)
,则公式返回: 1
我需要返回时: Hello
此公式应该对您有用:
=TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",LEN(A1))),MIN(INDEX(FIND(MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",ROW($1:$26),1),SUBSTITUTE(A1,".",REPT(" ",LEN(A1)))&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),)),LEN(A1)))
也许这个?
= mid(a1,ifError(find(",A1( 1,1(,ifError(find("。90((-1,90((