EXCELの数値を日付に「yydmをyyyy/dd/mmに」がとても難解だった。

前述 請求明細をExcel化の際に

PDFデータをExcelに貼ると

列区切りデータが消えて、A列目のデータにまとまってしまった。

これを日付、内容、金額に分けていく。

日付が例えば、19年10月1日の場合、19101という文字列に、

19年9月6日なら、1996に。これを6桁or8桁の日付に変えるのがかなり至難であった。

その変換過程を記録としていかに残す。

対象の内容は「日付＋本人(or家族)＋請求内容＋請求回数＋金額＋備考」のような並びですべて全角文字。

１．最初に先頭の数値とそれ以降を分離する。

2項目は「本人」が殆どなので「本人」を「,本人,」とカンマを入れ、後で「区切り位置」にてカンマでセルを分ける。

Cntl+Hで置換実行。

2.請求回数

請求回数がたまたますべて「１回」なので、こちらもカンマを入れて置換。

「１回」を「,1回,」に置換。

3. ここで区切り位置で分割

「日付」「本人」「請求内容」「請求回数」「金額＋備考」に分割された。

4. 全角文字列→数値化

この最初の「日付」を処理していく。

最初に日付の列を選択し、半角スペースを削除する置換を行うと半角数字に変換される。(理屈は不明)

5.年の2桁を削除する。

使った数式はB2セルを処理するとして

=IF(ISNUMBER(B2)=TRUE,IF(LEN(B2)=4,VALUE(MID(B2,3,1)&"0"&RIGHT(B2,1)),VALUE(RIGHT(B2,LEN(B2)-2))),"")

5.1 毎月末に合計数値を出してくれていて、この文字列は処理できなく、エラーを回避するため、最初に空白セルにするためにISNUMBERで判定を入れている。

5.2　 2桁の数値の場合を3桁に

例えば9月6日の場合、96になっており、8月13日は813である。

とにかく96を906に替えてしまう。

これですべて3桁以上の数値に。

つまりB2セルの数値の長さが4の時(4桁の数字の時 例：1996)、すなわち LEN(B2)=4の時は

MID(B2,3,1) にて3桁目から1桁を取り出し、その後&"0"&で0を続け、RIGHT(B2,1)で最後の1桁を付与している。そしてVALUEで文字列を数値化する。

例えば1996という数値があった場合に、この数値は長さが4であるため、この3桁目の9を取り出し、0を続けて90とし、右の一桁6を付け加えて906の数字としている。

5.3 数値の長さが4でない場合(4桁の数字でない 例19913)

文字の長さから２を引いて、LEN(B2)-2、そして右からこの数値を取り出す、RIGHT(B2,LEN(B2)-2)。

6. 補正

まず数式として出ている値に変換(コピーして値を貼り付け)。

この時に課題として残るのは、10月1日の101の3桁の数字だ。11月1日と1月11日は同じ111になっている。

請求書の位置づけで近しい数か月の請求がまとまっているので前後で11月か1月かは区別がつくため、手打ちで正しい3桁or4桁の数値に0を足していく。

これで月日が4桁の数値で表現された。最初のゼロは表れていないが、年を1万倍して足せばOKだ。

7. 年を付加する。

使った式は以下。少しずつ列をずらしているのD2セルを変換している。

=IF(ISNUMBER(D2),VALUE*1*10000+D2),"")

まずお約束の数値か否かで空白を出すかのISNUMBER処理をはじめに

次にB2セルの左から2桁が年のyy表示なのでLEFT(B2,2)で19などを取り出す。

今回は18年も含まれていたのでこれで網羅できる。

でこの年yyに2000を足して2019などにする。その年に1万をかけて出てきているmmddの4桁の数値に足したら20190906形式に変換が出来た。

8. スラッシュ区切りにする。

最後に20190906を2019/09/06に替える。使った式は以下。

=DATEVALUE(TEXT(F2,"00!/00!/00"))　。これと書式設定。

中々苦労したので、もっと簡単な方法があるはず。。。であるが自分としてはまずできたこと、夜遅くなったのでこれにて今日は満足。

*1:2000+LEFT(B2,2