エクセルで特定の文字の横のセルを抽出したい

写真のシート１の氏名と住所と電話番号を別のシートに


氏名　　住所　　　　　　　　　　　　　　　　　　　　電話番号
兼松　　〒891-0103 鹿児島県鹿児島市皇徳寺台　　　　099-111-1111
高野　　〒890-0041 鹿児島県鹿児島市　　　　　　　　090-1111-1111

上記のように表示するにはどうすれば良いですか？
写真添付のエクセルでは氏名がC　住所もC　電話番号はM となります。

No.2 回答者： yos1912 回答日時： 2020/01/10 20:47

名前の前に１～順番に番号がついていいのなら次の方法もあります。

元データはSheet1にあって、図の通りＥ列に名前と住所のデータ、Ｐ列に電話番号のデータが入っているとします(Ｃ列の名前という文字を呼び出しても意味がありませんから書かれていることは無視します)。
Sheet2に表を作るとして、A列：番号　Ｂ列：氏名　Ｃ列：住所　D列：電話番号とし２行目から１番目のデータが順番に並んるとします。各セルに入力する数式です。
Ａ２：1
Ｂ２：=index(Sheet1!$A:$P,2,(A2-1)*8+5)
Ｃ２：=index(Sheet1!$A:$P,３,(A2-1)*8+5)
Ｄ２：=index(Sheet1!$A:$P,2,(A2-1)*8+16)
これでいけると思います。２行目以後は同じ事の繰り返しです。Ａ列は１ずつ増やしてください。セルの結合が多いのは無駄な空白セルを作っていることになります。できればＣとかＥ列とかの列幅を変えて後を詰めた方が、参照先を間違えてもどこを参照したか一目でわかります。

No.1 回答者： tom04 回答日時： 2020/01/10 18:10

こんばんは！

画像を拝見するとセル結合が多いですね。
本来であればセル結合しない方が後々数式などで処理する場合、扱いやすいのですが・・・

取り合えず画像のように規則正しい配置になっている！という前提です。
VBAになりますが一例です。

元データはSheet1にあり、Sheet2に表示するとします。
標準モジュールにしてください。

Sub Sample1()
　Dim i As Long, cnt As Long
　Dim wS As Worksheet
　　Set wS = Worksheets("Sheet1")
　　　With Worksheets("Sheet2")
　　　　.Range("A:C").ClearContents
　　　　　With .Range("A1")
　　　　　　.Value = "氏名"
　　　　　　.Offset(, 1) = "住所"
　　　　　　.Offset(, 2) = "電話番号"
　　　　　End With
　　　　cnt = 1
　　　　　For i = 2 To wS.Cells(Rows.Count, "C").End(xlUp).Row Step 8
　　　　　　cnt = cnt + 1
　　　　　　　With .Cells(cnt, "A")
　　　　　　　　.Value = wS.Cells(i, "C").Offset(, 1)
　　　　　　　　.Offset(, 1) = wS.Cells(i + 1, "E")
　　　　　　　　.Offset(, 2) = wS.Cells(i, "P")
　　　　　　　End With
　　　　　Next i
　　　　　　.Activate
　　　　　　.Range("A:C").Columns.AutoFit
　　　End With
　　　　MsgBox "完了"
End Sub

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