使用单个公式删除所有不带脚本的变音符号，以便在所有单元格中使用，而无需调用Google API [英] Remove all diacritics without script with a single formula for use in all cells without making calls to Google API

问题描述

我创建了一个删除变音符号的公式，但需要通过ImportDATA进行呼叫.如果我在需要的每个单元格上都使用此公式，它将超出我可以进行的调用限制.它将锁定电子表格.

I created a formula that removes diacritics but needs to make calls through an ImportDATA. If I use this formula on every cell I need, it will exceed the call limit I can make. And it will lock the spreadsheet.

=ArrayFormula(if(exact(A1,Upper(A1)),Upper(VLOOKUP(A1,split(trim(transpose(split(query(SUBSTITUTE(regexreplace(regexreplace(query(IMPORTDATA("https://docs.oracle.com/cd/E29584_01/webhelp/mdex_basicDev/src/rbdv_chars_mapping.html"),"Select Col1 where Col1 contains '<td'",),"<td class=.+>","")&"~"&MOD(ROW(A:A),4),".+~[01]$",""),"~3","@"),"",9^6),"@"))),"~2"),2,0)),split(trim(transpose(split(query(SUBSTITUTE(regexreplace(regexreplace(query(IMPORTDATA("https://docs.oracle.com/cd/E29584_01/webhelp/mdex_basicDev/src/rbdv_chars_mapping.html"),"Select Col1 where Col1 contains '<td'",),"<td class=.+>","")&"~"&MOD(ROW(A:A),4),".+~[01]$",""),"~3","@"),"",9^6),"@"))),"~2")))

=ArrayFormula(if(exact(A1,Upper(A1)),Upper(VLOOKUP(A1,split(trim(transpose(split(query(SUBSTITUTE(regexreplace(regexreplace(query(IMPORTDATA("https://docs.oracle.com/cd/E29584_01/webhelp/mdex_basicDev/src/rbdv_chars_mapping.html"),"Select Col1 where Col1 contains '<td'",),"<td class=.+>","")&"~"&MOD(ROW(A:A),4),".+~[01]$",""),"~3","@"),"",9^6),"@"))),"~2"),2,0)),split(trim(transpose(split(query(SUBSTITUTE(regexreplace(regexreplace(query(IMPORTDATA("https://docs.oracle.com/cd/E29584_01/webhelp/mdex_basicDev/src/rbdv_chars_mapping.html"),"Select Col1 where Col1 contains '<td'",),"<td class=.+>","")&"~"&MOD(ROW(A:A),4),".+~[01]$",""),"~3","@"),"",9^6),"@"))),"~2")))

我想用A列将变音符号和B列对应的常用字母代替，而不用调用ImportDATA，因此无需在电子表格外进行调用，也不必进行更改即可进行更改创建一个也经常使用的新Script函数的方法，有一个限制使用的google限制...但是，当我在StackOverflow的3个答案的帮助下创建了此公式时，现在要进行此更改，我没有成功.

Instead of making this call to ImportDATA, I would like to use column A to put the diacritics and column B the corresponding common letters ... So make changes without having to make calls outside the spreadsheet and also without having to of creating a new Script function that is also often used, has a google limit that blocks usage ... But as I created this formula with the help of 3 answers here from StackOverflow, now to make this change I'm not succeeding.

最好是此公式已经在完整的列中进行了更改，而不是仅转换一个单元格.

And preferably that this formula already made the changes in a complete column instead of converting one cell only.

推荐答案

=ARRAYFORMULA(PROPER(IFERROR(REGEXREPLACE(L2:L, TEXTJOIN("|", 1, J2:J),
 VLOOKUP(REGEXEXTRACT(L2:L, TEXTJOIN("|", 1, J2:J)), J2:K, 2*
 SIGN(ROW(A2:A)), 0)&""), L2:L)))

这篇关于使用单个公式删除所有不带脚本的变音符号，以便在所有单元格中使用，而无需调用Google API的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！