いつもお世話になっております。
RfromLです。
今回はExcelのセル内にある文字列に対し、右側全角スペース埋めを行う関数についてです。
1.はじめに
以下のような表の「銘柄名」に対し右側全角スペース埋めにしたい時に使用する数式です。
今回は例として、銘柄名を文字数３０で右側全角スペース埋めにします。
ですが、このデータをみると半角文字を含むデータが存在します。
右側スペース埋めにする時、半角スペースであればデータの長さは一定になりますが半角文字のある項目に対し、文字数３０として右側全角スペースで埋めるとデータの長さがガタガタになってしまいます。
Excel上だとスペースが見えないので、文字数３０の範囲で右側全角スペース埋めしてテキストエディタにはってみてみると以下の通りです。
そこで、今回は半角文字を全角に変換しつつ、右側全角スペース埋めを行います。
2.数式と実例
書き方は以下の通りです。
=JIS(TRIM(文字列))&(REPT(" ",30-LEN(TRIM(JIS(文字列)))))文字列の部分には、セル番地を指定しても良いですが、今回は表なので列指定を使った方が楽です。
実際に例で示した表に右側全角スペース埋めを行ってみます。
手順１．数式を入力する列を追加します。
場所はどこでもいいですが、元となるセル列の隣に作った方が見やすいです。
手順２．数式の入力
今回は銘柄名のある列が「C列」なので２か所あるTRIM関数内の文字列指定箇所に「$C:$C」（列指定）を入力してC列を指定して、以下の数式をD列の対象行に入力します。
「=JIS(TRIM($C:$C))&(REPT(“ ”,30-LEN(TRIM(JIS($C:$C)))))」
数式を入力してエンターで確定すると、右側全角スペース埋めの文字列が設定されます。
テキストエディターに貼って内容を確認します。
元の文字列に対し、文字数３０で収まるように右側全角スペース埋めされていることがわかります。
手順３．対象行にコピーして反映
数式を入力したセルをコピーして、他の対象行に貼り付けて反映させます。
もちろんオートフィルで反映しても良いです。
貼り付けを行うと、他の行にも数式が反映されます。
文字数が長くなって全体が見えないので列幅を広げます。
データをテキストエディタに貼り付けて、ちゃんと数式が機能して右側全角スペース埋めになっているか確認します。
3.解説
3-1.使用した関数
今回作成した数式では、以下の関数を使用しています。
| 関数名 | 内容 |
|---|---|
| TRIM | 単語間のスペースを１つづつ残して、不要なスペースをすべて削除します。 |
| JIS | 半角の英数カナ文字（１バイト）を、全角の英数カナ文字（２バイト、DBCS）に変換します。 |
| REPT | 文字列を指定された回数だけ繰り返して表示します。この関数を使用して、セル幅全体に文字列を表示することができます。 |
| LEN | 文字列の長さ（文字数）を返します。半角と全角の区別なく、１文字を１として処理します。 |
3-2.数式の解説
[今回使用した数式]
=JIS(TRIM(文字列))&(REPT(" ",30-LEN(TRIM(JIS(文字列)))))この数式を分解して解説していくと以下のようになります。
①左側の「JIS(TRIM(文字列)」部分
元の文字列に対し全角、半角スペースを削除し半角文字があれば全角に変換します。
②右側の「(REPT(“ ”,30-LEN(TRIM(JIS(文字列)))))」部分
左側同様の全角・半角スペース削除、全角化を行ったあと、LEN関数で文字数を取得。
今回の指定文字数が３０文字なので、30からLEN関数で元文字列の文字数を引いて必要なスペースの数を取得します。
REPT関数で繰り返す文字列に” ”（全角スペース）を指定し、繰り返し回数部分にLEN関数で取得したスペースで埋める数を指定します。
③最後に「&」で「①」と「②」の数式の結果を文字列結合します。
これで、今回の数式が出来上がっています。
4.おわりに
今回は全角スペース埋めでしたが、REPT関数で繰り返し文字列を半角スペースにすれば右側半角スペース埋めにすることもできます。
使用場面としては、
ユーザーから追加して欲しいデータが手作業で作成されたファイルとして送られてきたときにデータベースのテーブルに挿入しようと思ったら、元ファイルが全角・半角混在であったり「テーブルの全角項目には全角スペース埋め」みたいなルールがあった時に今回の数式を使用して登録用のデータを整形したりします。
まあそんなにないことなのと、サクラエディタなどの高機能エディタでも出来ることなので、使用頻度はそんなに高くないかもですが知っておくと便利かなということで。
以上です。
宜しくお願い致します。
