指定した記号の前半と後半を取り出したい

質問

現在使っている関数で下記のものがあります。
セルA2に対象の文字列があるとして、■を基準に分けています。

=LEFT(A2,FIND("■",A2)-1)
=MID(A2,FIND("■",A2)+1,LEN(A2)-FIND("■",A2))

今回、これを元に編集したいのは
対象文字列に■が2個以上ある場合、最後の■を基準に分けたいのですが
どのように指定すればいいのか色々調べているのですが、分からず困っています。

【例】A2セルに
田中■けんじ■30歳■アメリカ

現状だと
=LEFT(A2,FIND("■",A2)-1)　　で
「田中」

=MID(A2,FIND("■",A2)+1,LEN(A2)-FIND("■",A2))　　で
「けんじ■30歳■アメリカ」

となりますが、今回は

「田中■けんじ■30歳」
「アメリカ」

と、最後の■の前後を抽出したいです。

参考情報として、対象文字で■は1個のものから最大10個位あるものまで存在します。

よろしくお願い致します。

回答No.3

KURUMITO · ベストアンサー

回答No1です。最後の文字列を取ったものも表示したいとのことでしたね。
仮にA2セルの文字列で最後の文字列は回答1の式で求められます。この回答1の答えをB2セルに表示させるとして最後の文字列を取った文字列をB3セルに表示させるとしたらB3セルには次の式を入力します。

=SUBSTITUTE(A1,"■"&B2,"")

回答1で後半の部分を回答2で前半の部分を取り出すことができますね。
■が10個以上ついても問題なく処理できます。

お礼

無事分割加工ができました。

大変助かりました。もっと勉強しないと。。。

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

回答No.6

tom04

こんにちは！
すでに回答は出ていますが、VBAでの一例です。
データはA2セル以降にあり、最後の「■」より前部分をB列に、
最後の「■」より後の部分をC列に表示するようにしてみました。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, str As String
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If InStr(Cells(i, 1), "■") > 0 Then
For k = Len(Cells(i, 1)) To 1 Step -1
str = Mid(Cells(i, 1), k, 1)
If str = "■" Then
With Cells(i, 2)
.Value = Mid(Cells(i, 1), 1, k - 1)
.Offset(, 1) = Mid(Cells(i, 1), k + 1, Len(Cells(i, 1)))
End With
Exit For
End If
Next k
Else
Cells(i, 2) = Cells(i, 1)
End If
Next i
End Sub 'この行まで

こんな感じではどうでしょうか？m（_ _）m

お礼

無事分割加工ができました。

今回は関数で作業しましたが、提案いただいたマクロも次回試してみます。

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

回答No.5

MackyNo1

No2の回答者ですが、参考までに。

もしTRIM関数を使って■の右側の文字を表示したいなら、以下のような数式にする方が簡単です。

=TRIM(RIGHT(SUBSTITUTE(A2,"■",REPT(" ",100)),100))

また、これまでのMIDを使った右側を取得する数式も、文字数をわざわざ関数で求めるのではなく、以下のように十分大きい数に設定する方が簡単です。
すなわち「FIND("■",A2)」の部分を提示したMAX関数に変更します。

=MID(A2,FIND("■",A2)+1,100)

お礼

一言関数と言っても、いろいろ方法がありますよね。
勉強になります。
ありがとうございました。

回答No.4

noname#192382

次のマクロを使えば望みのデータが抽出できます。

Sub Macro1()
'
' Macro1 Macro
' マクロ記録日 : 2013/3/6 ユーザー名 :
'
Dim kazu As Integer, ii As Integer

kazu = Len(Cells(2, 1)) 'A2のセルのデータの次数
For ii = 2 To kazu '1字ごとに"■"であるかどうかを後ろから2番目の字から前へ次々に調べる
'MsgBox Mid$(Cells(2, 1), (kazu - ii + 1), 1)
If Mid$(Cells(2, 1), (kazu - ii + 1), 1) = "■" Then
'MsgBox ii
Exit For '"■"が見つかったらループから抜け出る

Else
End If

Next 'ループの終わり
MsgBox Left$(Cells(2, 1), (kazu - ii)) '抽出の結果
'
End Sub

お礼

無事分割加工ができました。

今回は関数で作業しましたが、提案いただいたマクロも次回試してみます。

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

回答No.2

MackyNo1

左側の切り出し文字が５０文字以内なら、例えば以下のような数式で■の左の文字を取得できます。

=LEFT(A2,MAX(INDEX((MID(A2,ROW($1:$50),1)="■")*ROW($1:$50),))-1)

お礼

無事分割加工ができました。

大変助かりました。もっと勉強しないと。。。

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

回答No.1

KURUMITO

A2セルに文字が有るとしたら次のような式にします。

=TRIM(MID(SUBSTITUTE(A1,"■",REPT(" ",100)),(LEN(A1)-LEN(SUBSTITUTE(A1,"■","")))*100,100))

REPT関数の引数である" "は""の間に半角のスペースを入れます。 SUBSTITUTEの引数では半角を入れません。

お礼

無事分割加工ができました。

大変助かりました。もっと勉強しないと。。。

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