Excel 文字列の前後をスペースやゼロで埋める方法
Excelで文字列の前後をスペースやゼロで埋める方法です。
固定長データのファイルを作成する場合、文字列の後ろをスペースで埋めたいなどの要件がありますね。ちなみに固定長データとは、桁数でデータ項目を判断する電文データのことです。1レコードの1～8桁目を売上日(20160501)、9～23桁目を伝票番号(Z1234)、24～43桁目を商品コード(X01234)、44～50桁目を金額(9999)など、決まった場所にデータを配置する方法です。伝票番号と商品コードは後ろスペース埋め、金額は前ゼロ埋めなどと指定され、固定の桁数に合わせることがほとんどですね。
例： 20160501Z1234 X01234 0009999
今ではあまり採用されていないかもしれませんが、レガシーシステムでは固定長データの電文でやり取りされているものがほとんではないでしょうか。
実際はデータベースからレコードを取得して、各項目を加工しながらファイルを作成するようプログラミングするのですが、テストデータを作るのはやっぱり Excel の方が楽です。またプログラミング知識のない方でも、システムに関わる方であればサンプルデータを作るときに Excel で作れれば便利ですよね。
今回は Excelで文字列のスペース埋め、数値のゼロ埋めする方法 を紹介します。
文字列の右側にスペース埋め
伝票番号=Z1234に対して右側にスペースを埋め、15バイトにするためには、２つの Excel 関数を利用します。
- REPT(文字列,繰り返し回数)
- LEFTB(文字列, バイト数)
REPT関数は引数で指定した「繰り返し回数」分、繰り返した文字列を表示してくれます。LEFTB関数は引数で指定した「左から取り出したいバイト数」分、値を表示してくれます。
使い方はこんな感じです。
=LEFTB(A1&REPT(" ",15),15)
A1部分は指定のセルに書き換えてください。
A1に"X1234"という値があったと想定します。上記の関数の組み合わせは、REPT関数を使ってスペースを15バイト作ります。A1に値がなければスペース15バイトになりますね。A1と連結し、そこから LEFTB関数を使って左から15バイト取得するという意味になります。
下図はサンプルです。B列にはバイト数を求める LENB 関数を利用しています。
数値の左側にゼロ埋め
金額=9999に対して左側にゼロを埋め、7バイトにするためには、同じく ２つの Excel 関数を利用します。
- REPT(文字列,繰り返し回数)
- RIGHTB(文字列, バイト数)
RIGHTB関数は引数で指定した「右から取り出したいバイト数」分、値を表示してくれます。
使い方はこんな感じです。
=RIGHTB(REPT(0,7)&A1,7)
A1部分は指定のセルに書き換えてください。
A1に 9999 という値があったと想定します。上記の関数の組み合わせは、REPT関数を使ってゼロを7バイト作ります。A1に値がなければゼロが7バイトになります。A1と連結し、そこから RIGHTB関数を使って右からから7バイト取得するという意味になります。
下図はサンプルです。B列にはバイト数を求める LENB 関数を利用しています。
まとめ
Excelで文字列のスペース埋め、数値のゼロ埋めする方法を紹介しました。
いかがでしたか？固定長データの作り方、伝わりましたでしょうか？ちなみに今回の例を全部つなげるとこうなります。
=A2&LEFTB(B2&REPT(" ",15),15)&LEFTB(C2&REPT(" ",20),2０)&RIGHTB(REPT(0,7)&D2,7)
皆さんの固定長データサンプル作りに役立てばうれしいですね。
おつかれさまでした。