B2 中的印度分离器系统:
=ARRAYFORMULA(REGEXREPLACE(REGEXREPLACE(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(QUERY(
REGEXEXTRACT(REGEXREPLACE(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(QUERY(IFERROR(
REGEXEXTRACT(A2:A, REPT("(.)", IF(LEN(A2:A)=3, LEN(A2:A)-4, LEN(A2:A)-3))), "0"),
"select "&JOIN(",", "Col"&SORT(SEQUENCE(MAX(LEN(A2:A)-3)), 1, )))),,9^9)), " ", ),
"(.{2})", "$1,"), REPT("(.)", IF((LEN(A2:A)-3)+ROUNDDOWN((LEN(A2:A)-3)/2)<1, 1,
(LEN(A2:A)-3)+ROUNDDOWN((LEN(A2:A)-3)/2)))),
"select "&JOIN(",", "Col"&SORT(SEQUENCE(MAX((LEN(A2:A)-3)+
ROUNDDOWN((LEN(A2:A)-3)/2))), 1, )))),,9^9)), " ", ), "^,", )&","&IFNA(
REGEXEXTRACT(A2:A, "...$"), IF(A2:A="",,TEXT(A2:A, "000"))), "^0,$", ))
C2 中的印度空头货币:
=ARRAYFORMULA(IFNA(ROUND(A2:A*VLOOKUP(LEN(A2:A), {SEQUENCE(19),
{1; 1; 1; 1; 1; 10^-5; 10^-5; 10^-7; 10^-7; 10^-9; 10^-9;
10^-11; 10^-11; 10^-13; 10^-13; 10^-15; 10^-15; 10^-17; 10^-17}}, 2, 1), 2)&" "&
VLOOKUP(LEN(A2:A), {SEQUENCE(19),
{"Rp"; "Rp"; "Rp"; "Rp"; "Rp"; "L"; "L"; "Cr"; "Cr"; "Arab"; "Arab";
"Kharab"; "Kharab"; "Nil"; "Nil"; "Padma"; "Padma"; "Shankh"; "Shankh"}}, 2, 1)))
- 边注:
ROUND
设置为小数点后 2 位。这可以设置为 0,或者ROUND
可以完全删除,也可以替换为TRUNC
如果需要的话
电子表格演示 https://docs.google.com/spreadsheets/d/1jpXvZSDD2kBj1OnGQUIIBVzKcDUB240IGGQpm1bZDv0/edit#gid=0