使用单个公式删除所有变音符号，无需脚本即可在所有单元格中使用，而无需调用 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"),"选择Col1，其中Col1包含'<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"),"选择Col1，其中Col1包含'<td'",),"<td class=.+>","")&"~"&MOD(ROW(A:A),4),".+~[01]$",""),"~3","@"),"",9^6),"@"))),"~2")))

不是对 ImportDATA 进行调用，我想使用 A 列来放置变音符号，B 列中放置相应的常用字母...因此无需在电子表格之外进行调用即可进行更改并且不必创建一个也经常使用的新脚本函数，它有一个阻止使用的谷歌限制......但是当我在 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屋！