如图,要替换A3中的内容,但所谓“指定字符”是什么,公式中姑且把“id_”认定为指定字符,公式:
=SUBSTITUTE(SUBSTITUTE(A3,MID(A3,FIND("id_",A3)+3,1),UPPER(MID(A3,FIND("id_",A3)+3,1))),"_","")
追问指定字符是下划线,把所有下划线后面的字母变成大写
例:client_deli_message_id → clientDeliMessageId
建议简单地以“_"分列后,将首字母替换为大写字母,最后合并。方法简单、直观、易行。否则公式相当复杂,难度虽然不大。此外可考虑VBA。
普通版本,有多少个"_"就用多少个SUBSTITUTE嵌套,如A3中的两个,公式:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,MID(A3,FIND("_",A3)+1,1),UPPER(MID(A3,FIND("_",A3)+1,1))),MID(SUBSTITUTE(A3,MID(A3,FIND("_",A3)+1,1),UPPER(MID(A3,FIND("_",A3)+1,1))),FIND("_",SUBSTITUTE(A3,MID(A3,FIND("_",A3)+1,1),UPPER(MID(A3,FIND("_",A3)+1,1))),FIND("_",SUBSTITUTE(A3,MID(A3,FIND("_",A3)+1,1),UPPER(MID(A3,FIND("_",A3)+1,1))))+2)+1,1),UPPER(MID(SUBSTITUTE(A3,MID(A3,FIND("_",A3)+1,1),UPPER(MID(A3,FIND("_",A3)+1,1))),FIND("_",SUBSTITUTE(A3,MID(A3,FIND("_",A3)+1,1),UPPER(MID(A3,FIND("_",A3)+1,1))),FIND("_",SUBSTITUTE(A3,MID(A3,FIND("_",A3)+1,1),UPPER(MID(A3,FIND("_",A3)+1,1))))+2)+1,1))),"_","")
Excel的365版本,不管多少个"_",可用公式:
=SUBSTITUTE(TEXTJOIN(,1,SUBSTITUTE(TRIM(MID(SUBSTITUTE(A3,"_",REPT(" ",99)),COLUMN(A:Z)*99-98,99)),LEFT(TRIM(MID(SUBSTITUTE(A3,"_",REPT(" ",99)),COLUMN(A:Z)*99-98,99))),UPPER(LEFT(TRIM(MID(SUBSTITUTE(A3,"_",REPT(" ",99)),COLUMN(A:Z)*99-98,99)))))),LEFT(TEXTJOIN(,1,SUBSTITUTE(TRIM(MID(SUBSTITUTE(A3,"_",REPT(" ",99)),COLUMN(A:Z)*99-98,99)),LEFT(TRIM(MID(SUBSTITUTE(A3,"_",REPT(" ",99)),COLUMN(A:Z)*99-98,99))),UPPER(LEFT(TRIM(MID(SUBSTITUTE(A3,"_",REPT(" ",99)),COLUMN(A:Z)*99-98,99))))))),LOWER(LEFT(TEXTJOIN(,1,SUBSTITUTE(TRIM(MID(SUBSTITUTE(A3,"_",REPT(" ",99)),COLUMN(A:Z)*99-98,99)),LEFT(TRIM(MID(SUBSTITUTE(A3,"_",REPT(" ",99)),COLUMN(A:Z)*99-98,99))),UPPER(LEFT(TRIM(MID(SUBSTITUTE(A3,"_",REPT(" ",99)),COLUMN(A:Z)*99-98,99)))))))))
同时按Ctrl+Shift+Enter三键输入数组公式
图中B3为前面的公式,C后为后面的公式。