前回の応用です

Excel 令和01(元年も対応)年と書いてあるタイプの新旧元号日付文字列を日付に戻すセルの式
の応用です。
数字が全角で曜日が入っている
令和元年８月１７日（土）
を日付に変えます。

今回必要になるもの

全角の数字を半角に変える関数ASC関数

さらに日付だけ取り出す

Len(A1)-3
がポイントです。

令和元年８月１７日（土曜）だと4文字
令和元年８月１７日（土曜日）だと５文字
令和元年８月１７日（Sat）だと4文字
引くと日付がカットできます。

A1に令和元年８月１７日（土）という文字列が入っているときに日付に変換する関数

=IF(ISNUMBER(ASC(left(A1,Len(A1)-3))),ASC(left(A1,Len(A1)-3)),
IF(ISERROR(IF(OR(LEFT(ASC(left(A1,Len(A1)-3)),2)="昭和",LEFT(ASC(left(A1,Len(A1)-3)),2)="大正",LEFT(ASC(left(A1,Len(A1)-3)),2)="明治"),
DATEVALUE(SUBSTITUTE(ASC(left(A1,Len(A1)-3)),"元","01")),
DATEVALUE(REPLACE(SUBSTITUTE(ASC(left(A1,Len(A1)-3)),"元","01"),1,2,"平成")))),
DATE(MID(ASC(left(A1,Len(A1)-3)),3,FIND("年",ASC(left(A1,Len(A1)-3)))-3)+2018,LOOKUP(10,LEFT(MID(ASC(left(A1,Len(A1)-3)),FIND("年",ASC(left(A1,Len(A1)-3)))+1,FIND("月",ASC(left(A1,Len(A1)-3)))-FIND("年",ASC(left(A1,Len(A1)-3)))-1),COLUMN($1:$1))*1),LOOKUP(10,LEFT(MID(ASC(left(A1,Len(A1)-3)),FIND("月",ASC(left(A1,Len(A1)-3)))+1,FIND("日",ASC(left(A1,Len(A1)-3)))-FIND("月",ASC(left(A1,Len(A1)-3)))),COLUMN($1:$1))*1)),
IF(OR(LEFT(ASC(left(A1,Len(A1)-3)),2)="昭和",LEFT(ASC(left(A1,Len(A1)-3)),2)="大正",LEFT(ASC(left(A1,Len(A1)-3)),2)="明治"),
DATEVALUE(SUBSTITUTE(ASC(left(A1,Len(A1)-3)),"元","01")),
DATE(
YEAR(DATEVALUE(REPLACE(SUBSTITUTE(ASC(left(A1,Len(A1)-3)),"元","01"),1,2,"平成")))+30,
MONTH(DATEVALUE(REPLACE(SUBSTITUTE(ASC(left(A1,Len(A1)-3)),"元","01"),1,2,"平成"))),
DAY(DATEVALUE(REPLACE(SUBSTITUTE(ASC(left(A1,Len(A1)-3)),"元","01"),1,2,"平成")))
)
)))

マクロで入れる

たとえば上の式をマクロでコピペすると成功しますが、コードはエラーになります

オートで記録すると大変なことになります

Sub InsertFormulaIntoActiveCell()
ActiveCell.Formula = _
"=IF(ISNUMBER(ASC(left(A1,Len(A1)-3))),ASC(left(A1,Len(A1)-3)),IF(ISERROR(IF(OR(LEFT(ASC(left(A1,Len(A1)-3)),2)=""昭和"",LEFT(ASC(left(A1,Len(A1)-3)),2)=""大正"",LEFT(ASC(left(A1,Len(A1)-3)),2)=""明治""),DATEVALUE(SUBSTITUTE(ASC(left(A1,Len(A1)-3)),""元"",""01"")),DATEVALUE(REPLACE(SUBSTITUTE(ASC(left(A1,Len(A1)-3)),""元"",""01""),1,2,""平成""))))," & _
"DATE(MID(ASC(left(A1,Len(A1)-3)),3,FIND(""年"",ASC(left(A1,Len(A1)-3)))-3)+2018,LOOKUP(10,LEFT(MID(ASC(left(A1,Len(A1)-3)),FIND(""年"",ASC(left(A1,Len(A1)-3)))+1,FIND(""月"",ASC(left(A1,Len(A1)-3)))-FIND(""年"",ASC(left(A1,Len(A1)-3)))-1),COLUMN($1:$1))*1),LOOKUP(10,LEFT(MID(ASC(left(A1,Len(A1)-3)),FIND(""月"",ASC(left(A1,Len(A1)-3)))+1,FIND(""日"",ASC(left(A1,Len(A1)-3)))-FIND(""月"",ASC(left(A1,Len(A1)-3)))),COLUMN($1:$1))*1)),IF(OR(LEFT(ASC(left(A1,Len(A1)-3)),2)=""昭和"",LEFT(ASC(left(A1,Len(A1)-3)),2)=""大正"",LEFT(ASC(left(A1,Len(A1)-3)),2)=""明治""),DATEVALUE(SUBSTITUTE(ASC(left(A1,Len(A1)-3)),""元"",""01"")),DATE(YEAR(DATEVALUE(REPLACE(SUBSTITUTE(ASC(left(A1,Len(A1)-3)),""元"",""01""),1,2,""平成"")))+30" & _
",MONTH(DATEVALUE(REPLACE(SUBSTITUTE(ASC(left(A1,Len(A1)-3)),""元"",""01""),1,2,""平成""))),DAY(DATEVALUE(REPLACE(SUBSTITUTE(ASC(left(A1,Len(A1)-3)),""元"",""01""),1,2,""平成"")))))))"
ActiveCell.NumberFormat = "yyyy/mm/dd"
End Sub

ここでわかるFormulaR1C1の意味

マクロにはR1C1で記録されます。
もっと式が長くなります。
しかし、このように長大な式ではアドレスを変えるのも大変です。
しかし

行列	A	B	C
1	令和元年８月１７日（土）	B列に式
2	令和元年８月１７日（土）	B列に式	以下100行

このようなケースではB列にはつねに隣の式である R1C1形式が強いです。
式をR1C1形式で書くのはこうしたセルの距離が常に一定な場合に有効であると考えられます。

Excel 令和元年８月１７日（土）という文字列を日付に変える