インターネットバンキングのデータをExcelの関数やマクロで加工して会計ソフトへインポートする際に、「空白データのように見えて実はスペースが入力されている」という面倒くさいデータに遭遇することがありましたので、その対応方法を考えてみました。
目次
空白に見えるのに空白じゃないセルにはスペースが入力されているパターンが多い。
今回想定しているインターネットバンキングのデータを表現するとこんな感じのデータになります。
一見すると、Ｃ３セルとＣ６セル、Ｃ９セルについては空白に見えます。
ところがこのC列のＣ３～Ｃ９セル部分について空白セルを選択してみると
こんな感じのメッセージが表示されてしまいます。
で、よくよく調べてみるとこれらのセルについては「空白に見えるだけ」で実際にＣ３セルを選択してF2キーを押してみると
こんな感じで、カーソルが先頭ではなく、セルの真ん中あたりで点滅しています。
空白のセルであれば普通はカーソルはセルの先頭で点滅するはずなのに先頭以外のところにカーソルがあるということは
半角スペースか全角スペースがこのＣ３セルにデータとして入力されている状態が考えられます。
Excel関数のtrim関数で空白に見えるセルに入力されているスペースを除去する
今回のようなケースで空白に見えるセルに入力された半角スペースか全角スペースを削除するときにはtrim関数を使うと大体うまくいきます。
任意のセルに「=trim(c3)」と入力して確定します。すると見た目に変化はありませんが、空白が削除されます。
スペースが削除されたかどうかをif関数を使用して確認してみます。
結果、きちんとスペースが削除されているようです。
この仕組みを利用して、もしC列の摘要欄が空白なら「摘要欄が空白」と表示されるようにしてみます。
if関数とtrim関数を組み合わせるだけです。
もし、入金の時の空白と、出金の時の空白で区別したい場合にはこんな感じで処理してはどうでしょうか？
if関数とand関数を使用して処理します。
C列の摘要欄が空白かつ「D列」に金額が記載されていたら「空白出金」と表示します。
C列の摘要欄が空白かつ「E列」に金額が記載されていたら「空白入金」と表示します。
これやると数式が少し長くなるので抵抗感がでてしまいますが。。。。
こんな感じで処理すると空白に見えるけど実はスペースが入力されているデータを加工することができます。
Excelマクロで空白に見えるセルに入力されているスペースを除去する。
わざわざマクロを使わなくてもtrim関数を使用すると今回のようなケースは解決するんですが、もしマクロでやるならどうするか考えてみます。
ちなみに。
個人的にはマクロを使わずに関数でできることは関数でやる。それでもできないことはマクロを使って考えるというスタンスなので、なんでもかんでもマクロでやるというのはあまり好きではありません。
こんな感じでどうでしょうか？
Sub kuuhaku()
Dim i As Long
For i = 3 To 9
Range("h" & i).Value = Trim(Range("c" & i).Value)
Next i
End Sub
もし、入金の時の空白と、出金の時の空白で区別したい場合にはこんな感じで処理してはどうでしょうか？
Sub kuuhaku2()
Dim i As Long
For i = 3 To 9
Range("h" & i).Value = Trim(Range("c" & i).Value)
If Range("h" & i).Value = "" And Range("d" & i) > 0 Then
Range("h" & i).Value = "空白出金"
ElseIf Range("h" & i).Value = "" And Range("e" & i) > 0 Then
Range("h" & i).Value = "空白入金"
End If
Next i
End Sub
こんな感じになります。下の動画で確認していただければと思います。
ボタン１を押した時には空白を削除するマクロが動きます。
ボタン２を押した時は入金の空白と出金の空白を区別して摘要を作成するマクロが動きます。
まとめ
空白に見えるけど実はスペースが入力されている場合のインターネットバンキングのデータの加工方法を考えてみました。
trim関数で処理しても上手くいかない時はsubstitute関数を使ってみるとよいと思います。
いずれにせよこういうデータがでてくるとデータ加工の工程がひと手間増えるのでやめてほしいですね。
そもそもなぜ空白（何もデータが入っていない状態）のままにしておかないのかが謎です。
空白に見せかけてスペースのデータを入力するメリットもわかりません。
【本日の近況報告】
シーナアンドザロケッツの名曲レモンティーの元ネタがヤードバーズの「Train Kept A Rollin’」という曲だということをいまさらながら知りました。18年くらいシナロケのオリジナルだと思っていました。。。。
どーでもいいですが、シーナアンドザロケッツを聴くと芋焼酎の黒霧島を思い出してしまいます。
昔ギターの鮎川さんが黒霧島のCMに出てたからだと思います。
【本日の１曲】
Cwondo – Twwen
ラジオで流れてるのをたまたま聴いて知って、最近はこればっかり聴いています。
cwondoとかいて、コンドウと読むみたいですね。
打ち込みのリズムにボコーダーのようなボーカルが気にいっています。
この曲は個人的にはスーパーカーみたいな感じがして好きです。
だだっぴろい駐車場でひたすらドラムをたたいている（フリをしている）PVもなかなかいい感じです。
この曲が収録されたアルバムは1枚通してすごく気にいっています。
