Excel 文字列抜き出しについて

質問

図のようにA1のセルに半角空欄の混じった文字列が入っています。
半角空欄は[ _ ]赤のアンダーバーで表してあります。

B1～G1セルのように文字列を抜き出すにはどのような数式を入力したらいいのでしょうか？

回答No.8

Chiquilin

最初にバージョンを書かない場合 最新とみなしますよ。

=FILTERXML("<x><y>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"～","-")," ","",9)," ","",5)," ","</y><y>")&"</y></x>","//y["&{3,4,5,6,7,8}&"]")

回答No.7

msMike

[No.5]ですが、その後の検証で下記のように贅肉を除去した式でもOKです。
=TRIM(MID(REPT(" ",50)&(SUBSTITUTE(SUBSTITUTE($A1," ～","～")," ",REPT(" ",100))),1+(COLUMN(C1)-1)*100,100))
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓…
=TRIM(MID((SUBSTITUTE(SUBSTITUTE($A2," ～","～")," ",REPT(" ",100))),1+(COLUMN(C2)-1)*100,100))

回答No.6

imogasi

こういう区切りの文字をDelimiter,Separeter、区切り文字という。
今回は、区切り文字は半角スペース1文字と見た。CSVファイルなどはカンマが、項目の、区切り文字である。
ーー
これは、Split関数というのがあるので、VBA向きの課題だ。
VBAになじみがなくても、これのみの対処として下記を実行したらよい、と思う。
実質２行だから。
ーーー
エクセルでVBAを使えるようにする方法、VBE画面で標準モジュール画面の出し方は
、WEBなどで、勉強してください。実質は２行です。あとはA列セルでの繰り返し。
Sub rwst01()
For i = 1 To Range("A10000").End(xlUp).Row
p = Split(Cells(i, "A"), " ")
Range(Cells(i, 2), Cells(i, 10)) = p　’２と１０は、第2から第１０列までを受け皿としているということ。
Next i
End Sub
出てくる＃N/Aは、ホームー置換の画面で、置換前に１セル分の＃N/Aをコピーし、すべて置換で、＃N/Aはすべて、空白に変わる。
ーー
操作でもデーター切り位置ー区切り文字を半角スペースと指定。
でやれるが（説明略）、こちらが本則で、次が関数、その次がVBAだろう。
まず関数に頭が行く質問者が多いようだが、本来エクセルは、操作で使うのが第1と思う。

回答No.5

msMike

次式
=TRIM(MID(REPT(" ",50)&(SUBSTITUTE(SUBSTITUTE($A1," ～","～")," ",REPT(" ",100))),1+(COLUMN(C1)-1)*100,100))
を入力したセル B1 を右方にオートフィルするってぇ～のは駄目よ、ダメダメですか？
ただし、結果は下記のように若干異なりますが・・・

____B________C__________D ________E__F ________G_______
42124422 ミント 09/01～03/01 108 98 01/05～02/01

回答No.4

M_Sato

わたしだったら、sed.exeというストリームエディタをvector.co.jpから入手して解凍し、以下の手順でデータを変換します。

１）sed.exeをC:\に置いておく。
２）変換するA列のデータをdata.txtというテキストファイルで保存する。
３）以下の５行のsedスクリプトをconv.sedという名前のテキストファイルで保存する。
s/^[0-9]* [0-9]* //
s/ ～/-/g
s/ /,/g
s|0\([1-9]/\)|\1|g
s|/0|/|g
４）以下の１行のバッチファイルをconv.batという名前のテキストファイルで保存する。
c:\sed -f conv.sed data.txt >data.csv
５）conv.batをクリックするとdata.csvというファイルができる。
６）data.csvをエクセルで開いて、データが変換されたことを確認する。

【スクリプトの説明】
１行目：行頭から二つ目の空白までを削除する
２行目：「 ～」を「-」に置換する
３行目：空白をカンマに置換する
４行目：スラッシュの前の数字が０で始まっていたら０を削除する
５行目：スラッシュのあと数字が０で始まっていたら０を削除する
（逆スラッシュ\が表示されていたら半角の￥と読み替えてください）

回答No.3

kkkkkm

No.2の修正
Function mSplit(ByRef mRng As Range, ByVal Spstr As String, ByVal num As Long)
は最後のAs Variantが抜け落ちてました。以下に変更してください。
Function mSplit(ByRef mRng As Range, ByVal Spstr As String, ByVal num As Long) As Variant

回答No.2

kkkkkm

過去のQ&Aに以下がありました。

スペースとスペースの間の文字を抽出する関数
https://okwave.jp/qa/q7214729.html

3個分けるだけでかなり長くなってますので、質問の文字列だととんでもなく長くなるのではないかと思われます。

なので、ユーザー定義関数を作成する方法もあります。

エクセルのユーザー定義関数
https://kokodane.com/tec3_3.htm
作成方法は上記を参考にしてコードは以下を参考にしてください。

日付の部分が
09/01～03/01
で良ければ
以下のコード
Function mSplit(ByRef mRng As Range, ByVal Spstr As String, ByVal num As Long)
mSplit = Split(mRng, Spstr)(num - 1)
End Function

9/1-3/1
にならないと駄目なのでしたら
以下のコード
Function mSplit(ByRef mRng As Range, ByVal Spstr As String, ByVal num As Long)
Dim Temp As Variant

Temp = Split(mRng, Spstr)(num - 1)
If Left(Temp, 1) = "～" Then
Temp = Replace(Temp, "～", "")
End If
If IsDate(Temp) Then
Temp = Format(Temp, "m/d")
End If
mSplit = Temp
End Function

使い方は
=mSplit(対象セル,区切り文字列,左から何番目かを指定)

B1
=mSplit(A1," ",3)
C1
=mSplit(A1," ",4)
D1
09/01～03/01で良い場合
=mSplit(A1," ",5) & mSplit(A1," ",6)
9/1-3/1
にならないと駄目な場合
=mSplit(A1," ",5) & "-" & mSplit(A1," ",6)
以下略

回答No.1

noname#252888

FindとMIdを駆使するしかないんじゃないですかね。
例えばB1セルは
=MID(A1,FIND("_",A1,FIND("_",A1)+1)+1,FIND("_",A1,FIND("_",A1,FIND("_",A1)+1)+1)-FIND("_",A1,FIND("_",A1)+1)-1)

FINDが多すぎてわけが分からないので
私ならA列とB列の間に9列を非表示で追加して9個ある"_"の位置を出す関数を入れて関数を簡易化させます。