この記事ではExcelでセル書式が時刻・日時の数字を文字列に変換する方法を紹介します。
「当 9:00」→「09:00」と変換したい場合の方法です。
ただ単に置換すればいいんじゃないのと思われるかもしれませんが、書式が時刻になっていると思うような変換をしてくれないので悩ましいのです。
ちょっと長くなりますが、私が利用したケースを説明しておきましょう。(飛ばしてもOKです)
現状利用の勤怠管理システムのデータを新しい勤怠勤怠システムへ取り込んで、正しい給与計算結果となる勤怠データが出力できればゴールです。
ただ現状の勤怠管理システムから出力されるデータが「09:00」だと「540」と出力される謎仕様。
簡単に新しい勤怠管理システムへ取り込むデータが作成できません。
そこで勤務表をPDFで出力して、それをExcelに変換して、そこからデータを作成するというアクロバティックなことをしています。
しかも画面では「9:00」と見えているのに、PDFにすると「当 9:00」となります。
もちろんExcelに変換しても「当 9:00」と作成されます。
これを「当」のみ除外すると「9:00:00」となります
求めているのは「09:00」という形。
下3桁の「:00」を消しても、セルの書式設定が時刻になっているため、消すことができません。
どうすりゃいいんだと考えた末の処理をここから紹介します。
Excelでセル書式が時刻の数字を文字列に変換する方法
こちらを使ってください→時刻を文字列にExcelサンプルファイル
このデータ
こんなデータにしたい
置換機能を利用する
置換機能(Ctrl + H)を開きます。
検索する文字列に「当 」(当の後ろに半角スペース有)、置換後の文字列に「」(空)として、「すべて置換」を実行します。
この方法で「当 」が取り除かれます。
同じように「当」や「翌 」を「」と置換します。
余分な部位が取れて良さげに見えます。
ただセルの書式設定で見るとユーザー定義にて見た目がそのように見えているに過ぎません。
しかしセルA1を選択すると数式バーには「8:00:00」と表示されています。
区切り位置を利用して文字列にする
1.文字列にしたい列を選択します。
※注意点としては1列ごとでしか選択できません。
2.データ→区切り位置を選択。
3.区切り位置指定ウィザード1/3
「カンマやタブなどの区切り~」を選択します。
4.区切り位置指定ウィザード2/3
すべてのチェックは外します。
5.区切り位置指定ウィザード3/3
文字列にチェックを入れます。(これが重要!)
書式設定が文字列に変わり、「8:00:00」となりました。
書式設定で確認してみましょう。
文字列になっているのがわかります。
関数を使って編集する
関数を使って「08:00」にします。
関数式:LEFT(RIGHT("0"&目的のセル,8),5)
時間の頭に0を付け、右から8文字取得する関数を書きます。
頭に0を付けるのは「8:00:00」を「08:00:00」とするためです。
ただ「12:00:00」の場合、「012:00:00」となってしまうので、右から8文字を取得するため、right関数を使います。
次に「08:00」「12:00」とするために左から5文字取得するLeft関数を使います。
形式を指定して貼り付ける
関数を無効にするために貼り付け機能を使います。
セルをコピーします。
右クリックで表示されるメニューから「形式を指定して貼り付け(S)」を選択します。
「値(V)」にチェックを入れ、OKで貼り付けます。
「08:00」となりました。
まとめ
Excelでセル書式が時刻・日時の数字を文字列に変換する方法を紹介しました。
他にも応用が効くテクニックなので、ぜひ使えるようにしておきましょう。
私が書いた2冊目の本です。