改行コードやスペースを削除したい

エクセルでデータ処理中、セルによっては文字列の後に見えない改行コードやスペースがあってvlookup等の検索から漏れることがあります。

そのようなゴミを見つけたら、left関数等で処理してます。しかし列によっては文字数も違いますし、改行コードは1種類ではないようです。何より問題は、発覚するまで手付かずになる事です。

データ処理前に全セルに適用して、事前に削除する方法はないでしょうか？マクロは多少組めますが、ヤルコト自体が明確になってません。

No.1 ベストアンサー 回答者： 銀鱗 回答日時： 2015/05/30 08:15

関数ならclean関数やtrim関数を利用する。

clean関数は改行コードなど表示されない文字情報を削除してくれる。
trim関数は連続している余分な空白コード（スペース）を削除して空白コードを一つだけにしてくれる。
スペースがまったく必要ないならSubstitute関数でスペースを空白に置き換えればよい。

置換で置き換え削除するのであれば、改行コードはキャラクターコードの１０なので
アルファベットの10番目であるJをCtrlキーを押しながら入力すればいい。
具体的には
改行コードを削除したいセルを選択し（複数選択可）
Ctrl+Hなどで「検索と置換」のウインドウを表示させ、
「検索する文字列」にCtrl+J（何も表示されないが入力はできている）
「置換後の文字列」には何も指定せず（指定されている場合はDelキーやBSキーで内容を削除する）
「置換」ボタンをクリックする
だけだ。
余計なスペースを削除したい場合は「検索する文字列」にスペースを2つ、「置換後の文字列」にスペースを１つで「置換」を必要な回数繰り返せば最後にはスペースは１つになる。
スペースが必要ないなら「検索する文字列」にスペース１つ「置換後の文字列」に何も指定しなければよい。

この回答へのお礼

ありがとうございます。
スペースに2種類あるのは初耳です。詳細は調べてみます。

お礼日時：2015/05/30 18:26

No.3 回答者： いしい 回答日時： 2015/05/30 12:30

「見えない改行コード」がセル内の Alt+Enter で入力したものならば、

No.1の回答にあるように Ctrl+J を置換すれば良いようです。
次のサイトも参考になるかもしれません。

「Office TANAKA - Excel Tips[セル内の改行を削除する]」
http://officetanaka.net/excel/function/tips/tips …

削除したい「スペース」が通常のスペースではなくノーブレークスペースならば、
やはり、置換を使って、その「スペース」を削除できます。

また、ノーブレークスペースは、
CSV形式で出力して一部のテキストエディタで見ると「?」になります。

「ExcelでCSV保存したときに半角スペースがはてな（？）に文字化けする | TechRacho」
http://techracho.bpsinc.jp/baba/2010_07_22/2178

また、正体不明の文字コードもCSV形式で出力すると「?」になるかもしれませんので、
テキストエディタで「?」を削除してから戻したら良いのかもしれません。

Ctrl+J に相当する見えない改行コードをマクロで削除する場合、

Cells.Replace What:=Chr(10) , Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

で削除できました。
ノーブレークスペースをマクロで削除する場合、

Cells.Replace What:=ChrB(160), Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

で削除できました。
「マクロの記録」で記録して少し整理したものなので、意味は分かりませんが、
「=ChrB(160)」の数字をいろいろと変えたものを並べておくことで、
一括で削除できるかもしれません。

また、
「Chr(10)」だけでなく「Chr(13)」も置換した方が良いかもしれません。
その他、怪しげなものを置換するマクロを用意しておいて、
不足していたら、その都度追加するしかないかもしれません。

この回答へのお礼

ありがとうございます。
2種類のスペースは詳細調べてみます。

お礼日時：2015/05/30 18:34

No.2 回答者： tom04 回答日時： 2015/05/30 08:39

こんにちは！

＞データ処理前に全セルに適用して、事前に削除する方法はないでしょうか？
というコトですので、VBAでの一例です。

「半角スペース」・「全角スペース」・「改行」　だけの削除としています。

Sub Sample1()
Dim k As Long, myAry
myAry = Array(" ", "　", vbLf)
For k = 0 To UBound(myAry)
ActiveSheet.Cells.Replace what:=myAry(k), replacement:="", lookat:=xlPart
Next k
End Sub

※　当然のコトですが、データは実データだという前提です。
（数式によって表示されているデータではない）

単純に置換を3度行っているだけです。m(_ _)m

この回答へのお礼

ありがとうございます。
マクロ化の際は使わせて下さい。

お礼日時：2015/05/30 18:29