「""」内にある空白に「_」アンダーバーを挿入

質問

Excel2010での関数の質問です。

│ │ │ │ └─"ABC DE"　　←元データ

│ │ │ │ └─"ABC_DE"　　←完成形

「""」内にある半角の空白に「_」アンダーバーを
関数を使って挿入させたいと考えています。
(「""」内の文字は一意ではありません。)

=SUBSTITUTE(A11," ","_")
を使用したら、全ての空白に「_」アンダーバーが
挿入されてしまい、

│__│__│__│__└─"ABC_DE"

になってしまい、「|」の間にも「_」アンダーバーが
挿入されてしまいます。
(「|」と「|」の間は、半角空白が2つ入っています。)

括弧内の文字を抽出なら質問箱に沢山あるのを
確認していますが、括弧内の文字だけを変更は
見当たりませんでした。

「""」内にある半角の空白に「_」アンダーバーを
挿入させる関数をご教授下さい。
よろしくお願いします。

回答No.3

SI299792 · ベストアンサー

関数のみでやってみました。
B11 =FIND("""",A11)
C11 =FIND("""",A11,B11+1)
D11 =MID(A11,B11+1,C11-B11-1)
E11 =LEFT(A11,B11)&SUBSTITUTE(D11," ","_")&MID(A11,C11,100)
でE11 に結果が入ります。

これを１つの関数にすると、
=LEFT(A11,FIND("""",A11))&SUBSTITUTE(MID(A11,FIND("""",A11)+1,FIND("""",A11,FIND("""",A11)+1)-FIND("""",A11)-1)," ","_")&MID(A11,FIND("""",A11,FIND("""",A11)+1),100)
とやたら長くなります。メンテナンスが大変なので、私ならやりません。

両方とも、""で囲まれたのが１か所だけ、２か所目以降は無視されます。
２か所以上あるなら、VBA を使わないと無理です。

注意：最後の100 は100 文字あれば大丈夫だろうという想像です。文字列が長い場合、最後まで表示されないこともあります。その場合、数字を増やしてください。

お礼

SI299792様。
回答ありがとうございました。
セルのそれぞれに結果を入れるとどう言う動きをするのかが分かり
大変勉強になりました。
関数1つなら理解出来るのですが、ネストになるとお手上げで。
ネストの考え方を勉強する必要があると感じました。
確かに、関数としては「やたら長い」ですが、意図した回答になり
大変満足しています。
ありがとうございました。

補足

意図した回答になったので、
こちらの回答をベストアンサーとさせて頂きます。
ありがとうございました。

回答No.5

山田 太郎（@f_a_007）

【Excel関数版】"｜_"を元に戻す
=SUBSTITUTE(SUBSTITUTE(A1, " ","_"), "｜_", "｜ ")

　朝に同じ式を書いて数度テストしたのですが首尾良い結果を得られませんでした。そこで、CutStr()を使ってみました。で、夜にもう一度アタック。で、成功。CutStr()よりこっちですね。

お礼

f_a_007様。
3回の回答ありがとうございました。
私の書き方がダメなのか、意図した回答を得る事が出来ませんでした。
何度も回答して下さったのに、ごめんなさい。
ありがとうございました。

回答No.4

imogasi

=SUBSTITUTE(A1," ","_",1)
をＢ１に入れる。
Ａ１セルのデータ例　asd fghj　（ｆの前に半角スペース）－－＞Ｂ１の結果　asd_fghj
元データはＡ１セル。関数式は別セルに入れる（常識）、例Ｂ１。
最後の引数の１は、１番目に見つかったものを対象にする。
ーー
２，３番目も可能。その際は同じ関数でネストする。
例　=SUBSTITUTE(SUBSTITUTE(A2," ","_",1)," ","_",1)
他に置き換えた結果を対象にするので第4引数は１でよい。
Ａ２データ例　asd f ghj　（ｆの前後に半角スペース）　ー－＞　asd_f_ghj

お礼

imogasi様。
回答ありがとうございました。
試してみた所、
│__│__│__│__└─"ABC_DE"
と、なってしまい意図した結果が得れませんでした。
折角、回答下さったのに私の理解不足だと思います。
ごめんなさい。ありがとうございました。

回答No.2

山田 太郎（@f_a_007）

【訂正】
=CutStr(A1, """", 1) & """" & SUBSTITUTE(CutStr(A1, """", 2), " ", "_") & """"

左右を合成する際の 《& """"》の位置が間違っていました。

回答No.1

山田 太郎（@f_a_007）

Excel は操作経験のない70歳になる爺ですので考え方だけ・・・

　添付図の式は、次のようです。

=CutStr(A1, """", 1) & """" & SUBSTITUTE(CutStr(A1, """", 2) & """", " ", "_")

式では、

１、ダブルクォーテーションの左側と右側に分割。
２、ダブルクォーテーションの右側のみ置換。
３、左側と右側を合成。

しています。

　私は、ダブルクォーテーションの左側と右側に分割するのに以下のCutStr()を利用しています。そこをExcelの関数で行えばOKなんではないでしょうか・・・

Public Function CutStr(ByVal Text As String, _
　　　　　　　　　　　　ByVal Separator As String, _
　　　　　　　　　　　　ByVal N As Integer) As String
　　Dim strDatas() As String

　　strDatas = Split("" & Separator & Text, Separator, , 0)
　　CutStr = strDatas(N * Abs(N <= UBound(strDatas)))
End Function

お礼

f_a_007様

早々の回答ありがとうございます。

まだ始業前でデスクに付けないので、
始業後試してみます。

ありがとうございました。