こんにちは。いーかです。
Excelの「データ整形」で“なぜか置換できない／一致しない”を、最短で切り分けるメモです。

30秒まとめ（結論1行）

Ctrl+Jで改行置換できない時は、①CRLF混在 ②不可視文字（NBSP/タブ） ③前後スペース（全角/半角混在）を疑う。対処は「検出→一括掃除→値貼り付け」でOK。

先に安全ネット（事故防止）

いきなり全列に適用せず、まず1セル or 1列をコピーして別シートでテストすると安心です。
（戻せる＝心が折れない）

前提：Ctrl+Jは「LF（改行）」検索の近道

Excelの置換で Ctrl+J は、だいたい LF（CHAR(10)） を検索するショートカット。
だから“別種類の改行やゴミ”が混ざると、置換が効いたり効かなかったりします。

原因1：CRLF混在（CHAR(13)＋CHAR(10)）

症状

Ctrl+Jで一部は消えるのに、まだ改行が残る
CSVや別アプリ由来のデータで起きやすい

検出（TRUEなら混入）

LFある？ =ISNUMBER(SEARCH(CHAR(10),A1))
CRある？ =ISNUMBER(SEARCH(CHAR(13),A1))

対処（確実テンプレ）

=SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),""),CHAR(10)," ")

結果がOKなら コピー → 値貼り付け で確定。

原因2：不可視文字（NBSP / タブ）

ほとんどは NBSP（CHAR(160)）かタブ（CHAR(9)） が犯人です。
見た目は空白っぽいのに、検索・置換・一致判定がズレます。

症状

見た目は同じなのに =A1=B1 が FALSE
TRIMしても消えない空白がある（NBSPのことが多い）

対処（王道コンボ）

1) NBSPを普通のスペースへ

=SUBSTITUTE(A1,CHAR(160)," ")

2) タブをスペースへ（必要なら）

=SUBSTITUTE(A1,CHAR(9)," ")

3) 最後に TRIM＋CLEAN（まとめて掃除）

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

結果がOKなら 値貼り付けで確定。

原因3：前後スペース（半角/全角/連続空白）

症状

VLOOKUP/XLOOKUPで一致しない
並べ替えやフィルターで想定外の順になる
置換条件がハマらない（同じに見えるのに違う）

対処（全角スペースも潰す）

全角スペース（"　"）を半角へ寄せてからTRIMすると安定します。

=TRIM(SUBSTITUTE(A1,"　"," "))

迷ったらこれ（万能“整形ベース”）

改行（CR/LF）＋NBSP＋全角スペースをまとめて「人間が読める文字列」に寄せるテンプレです。

=TRIM(
  CLEAN(
    SUBSTITUTE(
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE(A1,CHAR(13),""),
        CHAR(10)," "),
      CHAR(160)," "),
    "　"," ")
  )
)

使い方（これだけ覚える）

B列に式 → 下までコピー → 結果をコピー → 値貼り付けで上書き。
これが最短で事故が少ないルートです。

今日の1アクション

まずは1セルでいいので、これだけ確認：

CHAR(13)（CR）が混ざってない？
CHAR(160)（NBSP）が混ざってない？
全角スペース "　" が混ざってない？

ミニクイズ（答えは末尾）

Q1. Ctrl+Jが主に検索しているのは CHAR(10) / CHAR(13) どっち？
Q2. Webコピペで混ざりがちな“見た目が空白”の犯人は？
Q3. 全角スペースを半角に寄せる置換はどれ？
Q4. 長い万能テンプレを現場で適用する“最短手順”は？

今日のひとこと（成長ログ）

「効かない」には理由がある。原因を3つに絞ると、Excelは急に解ける。

解答とくわしい解説

A1. CHAR(10)（LF）
A2. CHAR(160)（NBSP）
A3. =SUBSTITUTE(A1,"　"," ")
A4. 別列で式→下までコピー→結果を値貼り付けで上書き

メモ：Ctrl+Jが効かない時の原因トップ3（CRLF混在／不可視文字／前後スペース）