☆エクセルの一つのセルから特定の文字から始まる部分を抜き出（抽出）したいです☆

質問

おはようございます！エクセルでの文字列操作を教えてくださいm(__)m一つのセルに、「リンゴ120ABCミカン」といった具合に、「カタカナ、数字、アルファベット、カタカナ」という順番に入ったデータがあります。これは、「リンゴ80ABCDオレンジ」となったりします。前のカタカナは、全部共通です。（全部＝リンゴ）数字の桁は、2桁か3桁です。アルファベットや後ろに出てくるカタカナの個数の数の決まりはありません。これを、「リンゴ」「120」「ABC」「ミカン」という具合に、列をわけたいんです。その列は、並び替えに使いたいので、「リンゴ80ABCDオレンジ」の数字の箇所の場合、「80A」と抜き出してしまっても問題はありません。後ろに出てくるカタカナの方は、５種類しかないので、「ミ」の時は、「オ」の時は、という指定が可能です。後ろのカタカナを出すときに、=if(iserror(mid( ,find("ミ",・・・とかやってみたのですが、５種類ある場合、ifをどのようにしていくかがわかりませんでした(;_;)やりたいことがうまく説明できてないかと思いますが、この式でなくても、表示できれば、何でもウレシイです。助かります！どなたか、ご存知の方、是非教えてください。よろしくお願いいたしますm(__)m

回答No.1

mshr1962 · ベストアンサー

=MID(A2,MIN(FIND({"ミ","オ","ス","パ","イ"},A2&"ミオスパイ")),LEN(A2))

お礼

mshr1962さん、こんばんは！お礼、大変遅くなってしまってすみませんm(__)mなるほどです！バッチリ、抜き出せました♪ありがとうございました☆

回答No.4

Wendy02

A列に文字列があるとします。

A1:リンゴ120ABCミカン
（ただし、全角のカタカナ文字列と半角の英数字の混合した文字列とします。また、それぞれの検索文字列の長さは、20字にしましたので、短いようでしたら、ROW($A$1:$A$50) などとして、長めにしても構いません。上限は、255までです。)なお、数式の中にあるINDEX関数は、配列をワークシートに取り扱えるようにするために使っています。

B1:
=LEFT(A1,MATCH(FALSE,INDEX(ISERROR(MID(A1,ROW($A$1:$A$20),1)*1),,),0)-1)

C1:
=LEFT(SUBSTITUTE(A1,B1,""),MATCH(TRUE,INDEX(ISERROR(MID(SUBSTITUTE(A1,B1,""),ROW($A$1:$A$20),1)*1),,),0)-1)

D1:
=LEFT(SUBSTITUTE(A1,B1&C1,""),LENB(JIS(SUBSTITUTE(A1,B1&C1,"")))-LENB((SUBSTITUTE(A1,B1&C1,""))))

E1:
=SUBSTITUTE(A1,B1&C1&D1,"")

面倒でしたら、VBAで作ったほうが早いかもしれませんね。

お礼

Wendy02さん、こんばんは！お礼、大変遅くなってしまってすみませんm(__)mなるほどです！たくさん式を考えてくださいまして、ありがとうございました♪スゴイですん！バッチリ抜き出せました♪ありがとうございました☆

回答No.3

KenKen_SP

こんにちは。KenKen_SP です。

> 表示できれば、何でもウレシイです。

とのことなので、関数ではなく VBA です。

【注 意】
マクロは「やり直し」がきかないので、必ずバックアップを取ってから実行
して下さい。

【手 順】
1.　[Alt]+[F11] で Visual Basic Editor （以下 VBE ）起動
2.　VBE メニュー[挿入]-[標準モジュール] クリック
3.　2. で開いた場所に下記ソースコードをコピペ
4.　VBE 閉じ、Excel 画面に戻る
5.　分割したいセル範囲を選択（ 1 列のみ）
6.　[Alt]-[F8] で、マクロ Main を実行

'【ソースコード】
Option Explicit
Private mRegExp　　 As Object ' REGEXP

Sub Main()

　　If UCase$(TypeName(Selection)) <> "RANGE" Then Exit Sub
　　If Selection.Columns.Count > 1 Then Exit Sub
　　If Application.CountA(Selection) = 0 Then Exit Sub

　　Dim C　　　　　 As Range
　　Dim strKana　　 As String
　　Dim strPattern　As String
　　Dim Buffer　　　As Variant

　　' マッチングパターン
　　strKana = "([ー\u30A0-\u30FF]+)"
　　strPattern = "^" _
　　　　　　　　 & strKana _
　　　　　　　　 & "([0-9０-９]+)" _
　　　　　　　　 & "([a-zA-Z]+)" _
　　　　　　　　 & strKana _
　　　　　　　　 & "$"
　　On Error GoTo ERROR_HANDLER
　　Application.ScreenUpdating = False
　　For Each C In Selection
　　　　Buffer = RegStrExtract(C.Value, strPattern)
　　　　If IsArray(Buffer) Then
　　　　　　With C.Offset(0, 1).Resize(1, UBound(Buffer) + 1)
　　　　　　　　.NumberFormat = "@"
　　　　　　　　.Value = Application.Transpose(Application.Transpose(Buffer))
　　　　　　End With
　　　　End If
　　Next C

TERMINATE:
　　Set mRegExp = Nothing
　　Exit Sub
ERROR_HANDLER:
　　MsgBox Err.Description, vbExclamation
　　Resume TERMINATE

End Sub

' サブマッチを配列でかえす
Private Function RegStrExtract( _
　　ByRef strSource As String, _
　　ByRef strPattern As String) As Variant

　　Dim MC　　　　　 As Object ' MatchCollection
　　Dim M　　　　　　As Object ' Match
　　Dim strKana　　　As String
　　Dim Buffer()　　 As String
　　Dim i As Long, j As Long

　　If mRegExp Is Nothing Then
　　　　Set mRegExp = CreateObject("VBScript.RegExp")
　　End If
　　With mRegExp
　　　　.Pattern = strPattern
　　　　.IgnoreCase = False
　　　　.Global = True
　　　　Set MC = .Execute(strSource)
　　End With
　　j = 0
　　If MC.Count > 0 Then
　　　　For Each M In MC
　　　　　　For i = 0 To M.SubMatches.Count - 1
　　　　　　　　ReDim Preserve Buffer(j)
　　　　　　　　Buffer(j) = M.SubMatches(i)
　　　　　　　　j = j + 1
　　　　　　Next i
　　　　Next M
　　　　RegStrExtract = Buffer
　　Else
　　　　RegStrExtract = vbNullString
　　End If

End Function

お礼

KenKenSPさん、こんばんは！お礼、大変遅くなってしまってすみませんm(__)m大変長いVBAの式を考えてくださいまして、本当にありがとうございましたm(__)m私には、意味はわかりませんが、コピペはできました^^;すごいですね☆☆☆ありがとうございました♪

回答No.2

mu2011

次の方法は如何でしょうか。
（１）データを並び替え（降順）を行うと同一桁でグループされる
（２）グループ（同一桁）範囲を選択し、データ→区切り位置→「スペースによって・・」を選択し次へ→データプレビュー上で各データ境界で左クリック→完了

お礼

mu2011さん、こんばんは！お礼、大変遅くなってしまってすみませんm(__)mなるほどです！そういう方法もあるのですね！とても参考になりました。ありがとうございました☆