求一个汉字年月日转数字年月日的自定义函数

hcm19522

C2=IFERROR(LOOKUP(1,0/FIND(SUBSTITUTE(TEXT(ROW($1:$99),"[DBNUM1]"),"一十","十")&"年",A2),ROW($1:$99)&"年"),"")&IFERROR(LOOKUP(1,0/FIND(SUBSTITUTE(TEXT(ROW($1:$99),"[DBNUM1]"),"一十","十")&"个月",A2),ROW($1:$99)&"个月"),"")&IFERROR(LOOKUP(1,0/FIND(SUBSTITUTE(TEXT(ROW($1:$99),"[DBNUM1]"),"一十","十")&"天",A2),ROW($1:$99)&"天"),"")

jiangxiaoyun

第一反应是用numberstring函数做一个数字转换数据库，然后替换。

天狼骑士2013

牛啊公式水平

天狼骑士2013

zxsea_7426 发表于 2022-12-10 20:40

花了二个小时把代码看懂了，逻辑水平太高了，不是一般人能写出得

准提部林

自訂函數//
一路replace下去
Function Chn_Num(xS As String) As String
xS = Replace(Replace("|" & xS, "年", "年|"), "月", "月|")
xS = Replace(xS, "|十", "一")
xS = Replace(Replace(xS, "十", ""), "|", "")
For i = 1 To 9
    xS = Replace(xS, Mid("一二三四五六七八九", i, 1), i)
Next i
Chn_Num = xS
End Function

grf1973

数字不是太大的话可以。超过十万就提取错误了。