【エクセル区切り位置】「列のデータ形式」で数字データを文字列・日付形式に変換する


区切り位置3アイキャッチ

こんにちは!もり(@moripro3)です!

エクセルの隠れ優等生「区切り位置」の魅力をシリーズを通して紹介しています!

前回の記事では、「各フィールドの値がダブルクォートで囲まれているカンマ区切りのデータ」を整形する方法を紹介しました。

  • ダブルクォートを削除する
  • 各フィールドをカンマで区切る
【エクセル区切り位置】ダブルクォートで囲まれたカンマ区切りのデータを一瞬で分割する
エクセルの「区切り位置」を紹介するシリーズ。第二回目は「各フィールドの値がダブルクォートで囲まれているカンマ区切りのデータ」のダブルクォートを削除し、カンマで分割する方法を紹介しています。

今回の記事では、区切り文字で区切ったあとの「列のデータ形式」を設定する方法を紹介します。

  • 先頭がゼロではじまる数字(電話番号など)
  • 日付を表す8ケタの数字

これらの値が含まれるデータをそのまま区切ると見た目がよくないので、今回のテーマは「書式を設定して見た目をキレイに整えよう」です!

区切った後に「列のデータ形式」の設定が必要なケース

なんちゃって個人情報というサイトから取得したダミーデータを使って紹介していきます。

※今回の記事から「区切り位置」の基本操作は省略していますので、操作手順はシリーズ前回までの記事をご覧ください。

名前・ふりがな・性別・年齢・誕生日・携帯の6フィールドを持つ、カンマ区切りのテキストファイルです。

区切り位置3-1

 

このデータをカンマで区切るため、まずはデータをエクセルシートに貼り付けます。

6列目「携帯」のフィールドに注目してください。勘の良い方、イヤな予感がしていませんか・・・?

区切り位置3-2

 

「携帯」のデータはハイフンなしの数字のみなので、これまでどおりの手順で操作すると、先頭のゼロが消えてしまいます。

区切り位置3-3

 

このような場合に「列のデータ形式」で書式を設定すると、見た目を整えることができます。

 

なぜ先頭のゼロが消えてしまうの?

0120-00-0000という電話番号をハイフンなしにすると「0120000000」となります。

数字のみで構成される「0120000000」は、数値の「120,000,000(一億二千万)」であると、エクセルが判断するからです。

先頭がゼロの数字データを文字列に変換する

先頭のゼロを表示させるために、携帯の数字データを「文字列」と認識させます。

テキストファイルのデータをエクセルシートに貼り付けたところからの解説です。

「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選択して「次へ」

区切り位置3-4-1

 

区切り文字「カンマ」を選択して次へ進みます。

区切り位置3-4-2

 

「列のデータ形式」の初期値は「G/標準」

「区切り位置」では、区切ったあとの各フィールドのデータ形式が、初期値で「G/標準」となっています。

「G/標準」では、このように自動で書式が設定されます。

  1. 数字 → 数値
  2. 日付 → 日付形式
  3. その他 → 文字列

「ハイフン無しの電話番号」は数字と判断され、数値形式に変換されるのです。

区切り位置3-4-3

 

列のデータ形式を「文字列」に変更する

列のデータ形式「G/標準」を「文字列」に変更しましょう。

ここで変更したいのは「携帯」の列ですね。データのプレビュー欄で列をクリックすると、白黒が反転します。

区切り位置3-5

 

次に、列のデータ形式選択欄で「文字列」を選択します。データのプレビュー欄の「携帯」のヘッダーが「文字列」に変わったことを確認したら完了をクリック。

区切り位置3-6

 

「携帯」の列が文字列として認識され、先頭のゼロが消えずに残りました。

区切り位置3-7

 

このように、商品コード・部署コードなど、先頭がゼロで始まるフィールドが存在する場合は、「列のデータ形式」で文字列に変換して、見た目を整えることができます。

8ケタの数字データを日付形式に変換する

続いてのサンプルデータがこちらです。「誕生日」の日付データにスラッシュが無く、8ケタの数字になっている場合です。

区切り位置3-8

 

列のデータ形式を「G/標準」のままで区切ると、そのまま取り込まれますね…

区切り位置3-9

 

元データに日付を表すスラッシュが無いけれど、区切った後のデータは日付形式にしたい!
そんな時に便利な設定を紹介します!

列のデータ形式を「日付」に変更する

5列目の「誕生日」のフィールドを日付形式に変更します。

列のデータ形式を設定する画面で、「誕生日」の列を選択してから、「日付(YMD)」を選択して完了をクリック。

区切り位置3-10

 

これで、区切ったあとの「誕生日」がスラッシュ付きの日付形式になりました。

区切り位置3-11

「区切り位置」の応用的な使い方

「区切り位置」は、カンマ・スペースなどで区切られているデータを分割するための機能ですが、「列のデータ形式」を設定する機能を利用して、こんなこともできます。

シートのA列に日付を表す8桁の数字があります。1列だけなので区切る要素ないですよね…?

これを、「区切り位置」を使用してスラッシュ付きの日付形式にできるのです!

区切り位置3-12

 

データを区切る作業ではないので、区切り文字のチェックはすべて外します。

区切り位置3-13

 

列のデータ形式を「日付(YMD)」に変更しましょう。

区切り位置3-14

 

A列の8桁の数字が日付形式に変換されました!

区切り位置3-15

 

本来、「区切り位置」はデータを区切るために使う機能ですが、工夫次第でこのような使い方もできます。

日付の扱いに困っている方はぜひ使ってみてくださいね!

まとめ

今回の記事では、「区切り位置」でデータを区切ったあとの「列のデータ形式」を設定する方法を紹介しました。

  • 先頭がゼロで始まる数字データを文字列にして、ゼロを表示させる
  • 8ケタの数字をスラッシュ付きの日付形式にする

「区切り位置」のシリーズはこれにて終了です。

ダブルクォートやシングルクォートで囲まれているデータ、スペース・カンマ・タブ区切りなどのデータは、「区切り位置」の機能を使用して一瞬で分割・整形しましょう!

連載目次:エクセル「区切り位置」を使い倒す

スペースが含まれるデータをスペース区切りで分割する。システムからダウンロードしたカンマ区切りのデータファイルを整形してエクセルに取り込む。

そんな作業が、プログラミング不要・エクセル関数不要で解決できる「区切り位置」の魅力を存分に紹介しています!

  1. 【エクセル区切り位置】スペースが含まれるデータを一瞬で分割する
  2. 【エクセル区切り位置】ダブルクォートで囲まれたカンマ区切りのデータを一瞬で分割する
  3. 【エクセル区切り位置】「列のデータ形式」で数字データを文字列・日付形式に変換する

  投稿者プロフィール

もり
もりITライター
GoogleAppsScript, VBAを専門とするITライターです。

退屈なことはプログラミングで片づけよう!
自らの事務職経験を活かし、事務作業をとことんラクにできるITネタを発信していきます。

個人ブログ『もりさんのプログラミング手帳』も運営中!

お気軽にTwitterフォローしてくださいね!

タイトルとURLをコピーしました