エクセルの質問：特定の文字列の有る無しで行を分ける

質問

エクセルの質問です。

１．姓名分割

姓名が一緒になっているリストがあります。
姓の後ろには：姓
名の後ろには：名と書かれています。

例
「六川:姓
六三郎:名
録太郎:名
六之助:名
六郎:名
六角:姓
六本木:姓
和井田:姓」

これをエクセルに張り付けて、
姓名を別々の行に分けることはできますでしょうか。

２．姓名で分割したものを、更に姓名で組み合わせたリストを作ることはできますか。

例
「六川　六三郎
六川　録太郎
穴川　六之助」
等

回答No.4

tom04

こんばんは！
すでに回答は出ていますが、VBAでの一例です。

Sheet1のA列にあるデータをSheet２のA列に表示するようにしてみました。

Alt＋F11キー　→　メニュー　→　挿入　→　「標準モジュール」を選択　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, cnt As Long, str As String
Dim wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")
wS2.Range("A:A").ClearContents
For i = 1 To wS1.Cells(Rows.Count, 1).End(xlUp).Row
If InStr(wS1.Cells(i, 1), "姓") > 0 Then
str = Replace(wS1.Cells(i, 1), ":姓", "")
If InStr(wS1.Cells(i + 1, 1), "名") > 0 Then
k = i + 1
Do While InStr(wS1.Cells(k, 1), "名") > 0
cnt = cnt + 1
wS2.Cells(cnt, 1) = str & " " & Replace(wS1.Cells(k, 1), ":名", "")
k = k + 1
Loop
Else
wS2.Cells(Rows.Count, 1).End(xlUp).Offset(1) = str
End If
cnt = wS2.Cells(Rows.Count, 1).End(xlUp).Row
End If
Next i
End Sub 'この行まで

※　コード内の「:」コロンは半角としていますので、実データが全角のコロンであれば訂正してください。

こんな感じではどうでしょうか？m(_ _)m

回答No.3

chie65536（@chie65535）

A1～A8セルに以下のように貼り付ける。

六川:姓
六三郎:名
録太郎:名
六之助:名
六郎:名
六角:姓
六本木:姓
和井田:姓

B1セルに
=IF(MID(A1,LEN(A1)-1,2)=":姓",MID(A1,1,LEN(A1)-2),"")
と入力。

C1セルに
=IF(MID(A1,LEN(A1)-1,2)=":名",MID(A1,1,LEN(A1)-2),"")
と入力。

D１セルに
=IF(B1<>"",B1,OFFSET(D1,-1,0))
と入力。

E1セルに
=IF(C1="","",D1&" "&C1)
と入力。

B１～E1セルをコピーして、下方向に必要なだけ連続コピーする。

六川:姓　　　六川　　　　　　　　　　　六川
六三郎:名　　　　　　　　 六三郎　　　六川　　　　六川 六三郎
録太郎:名　　　　　　　　録太郎　　　六川　　　　 六川 録太郎
六之助:名　　　　　　　　六之助　　　六川　　　　六川 六之助
六郎:名　　　　　　　　　六郎　　　　　六川　　　　六川 六郎
六角:姓　　　六角　　　　　　　　　　　六角
六本木:姓　六本木　　　　　　　　　　六本木
和井田:姓　和井田　　　　　　　　　　和井田

上記のようになっていればオッケー。

これで、B列には「姓だけリスト」が、C列には「名だけリスト」が出来る。

B、C列をコピー＆ペースト（形式選択貼付で「値のみ」で貼り付け）して、空白セルを詰めればオッケー。

D列は作業用なので使わない（「：名」の時に、直前の苗字を拾ってくるための式になっている）

E列は、苗字＋半角スペース＋名前を作っているので、E列をコピー＆ペースト（形式選択貼付で「値のみ」で貼り付け）すれば、姓名が組み合わされたリストが出来る。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

B2セル 不要なスペース
=TRIM(A2)

C2セル 「姓」を判断し、右2文字を取る。「姓」ではないなら上のセルを持ってくる
=IF(RIGHT(B2,1)="姓",LEFT(B2,LEN(B2)-2),C1)

D2セル 「名」を判断し、右2文字を取る。「名」ではないなら空白
=IF(RIGHT(B2,1)="名",LEFT(B2,LEN(B2)-2),"")

B2:D2セル範囲を下へオートフィル

回答No.1

kybo

その「リスト」がテキストデータだとして、メモ帳で開いて、普通にコピーします。
Excelを開いてA2のセルで形式を選択して貼り付け→テキストでOKします。

提示のリストには、文字の最後に半角スペースがありますので、A列選択、（Excel2007以降の手順）ホーム→検索と選択→置換→検索する文字列に半角スペース、置換後の文字列は空欄のまま、オプションで、セルの内容が完全に同一・・はチェックを外してすべて置換

※実際の動作の後ろに半角スペースが無ければ、置換は不要な操作です。

B2のセルには以下

=IF(RIGHT(A2,1)="姓",LEFT(A2,LEN(A2)-1),B1)

C2には以下の式を入れ、下へコピーします。

=IF(RIGHT(A2,1)="名",LEFT(A2,LEN(A2)-1),"")

最後にD2には以下の式を入れ、下へコピーします。

=IF(AND(B2<>"",C2<>""),B2&" "&C2,"")