excelで西暦抜き出し＞加工＞ソート後表示

質問

内部に形式(****)で西暦を記載したデータがあります。
　西暦に位置は不規則ですが前後に半角の()があります。

例　：
　　山本薩夫監督による『忍びの者』(1962年)は作品　　(以下省略）
　　続・忍びの者』（ぞく しのびのもの）は、(1963年)公開の日本映画 （以下省略）
吸血鬼ドラキュラは(1958年)のイギリスのハマー・フィルム・プロダクション

このようなデータが約３００行あります。
　　　　　　　　　（これからも増えます）

このデータから年号のみを比較して一部加工(西暦抜き出し）して
　年代別にソートしたいのですがどうすれば良いでしょうか？

excel上で処理を行いたいので方法を伝授下さい。

上記の例で加工したとしたら、
年代（A列）　データ（B列）　の順番で下記のようになるようにしたいです。

1958 吸血鬼ドラキュラは(1958年)のイギリスのハマー・フィルム・プロダクション
1962 山本薩夫監督による『忍びの者』(1962年)は作品
1963 続・忍びの者』（ぞく しのびのもの）は、(1963年)公開の日本映画

宜しくお願いします。

回答No.7

mshr1962 · ベストアンサー

No.2です。年がないなら
A2=MID(B2,FIND(")",B2)-4,4)*1
と年を消すだけでOKです。

お礼

お礼が遅れてすいませんでした。

12月に入り毎日、残業でネットに接続する時間もままならず。
検証する時間もなかなか思うようにならない状態が続いていましたが
やっと思うような処理が出来ました。

改めてお礼申し上げます。

回答No.9

keithin

B2に
「吸血鬼ドラキュラ(1958)」
と記入してあるなら
A2に
=-RIGHT(B2,6)
と記入してリスト下端までコピーし，並べ替えます。

回答No.8

noname#204879

添付図参照
Sheet1!B2: =MID(A2,FIND("年)",A2)-4,4)+ROW()/1000

Sheet2 の範囲 A2:C2 を選択(アクティブセルは A2)して、次の[条件付き書式]を設定
　　数式が　　　　=ISERROR(A2)
　　フォント色　　白

Sheet2!C2: =SMALL(Sheet1!B:B,ROW(A1))
Sheet2!A2: =INT(C2)
Sheet2!B2: =INDEX(Sheet1!A:A,MATCH(C2,Sheet1!B:B,0))
範囲 A2:C2 を下方にズズーッとドラッグ＆ペースト

お礼

お礼が遅れてすいませんでした。

12月に入り多忙でネットに接続出来る時間が取れませんでした。

回答No.6

tom04

No.5です！

西暦は（1962）のようになっているというコトですので・・・
前回のコードの少し手を加えてみました。
（括弧や数値は全角でも半角でも対応できます）

もう一度手を加えたコードを載せておきます。

Sub test() 'この行から
Dim i, k As Long
Dim str1, str2 As String
Dim ws As Worksheet
Set ws = Worksheets(2)
ws.Cells.ClearContents
Application.ScreenUpdating = False
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For k = 1 To Len(Cells(i, 1))
str1 = StrConv(Mid(Cells(i, 1), k, 6), vbNarrow)
str2 = Mid(str1, 2, 4)
If IsNumeric(str2) And Right(str1, 1) = ")" Then
With ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = str2
.Offset(, 1) = Cells(i, 1)
End With
End If
Next k
Next i
Application.ScreenUpdating = True
ws.Columns("A:B").Sort key1:=ws.Cells(1, 1), order1:=xlAscending
ws.Columns.AutoFit
End Sub 'この行まで

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

回答No.5

tom04

こんばんは！

VBAでの一例です。
Sheet1のデータはA列に入っていて、1行目からデータがあるとします。
Sheet2に表示するようにしてみました。

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

Sub test() 'この行から
Dim i, k As Long
Dim str1, str2 As String
Dim ws As Worksheet
Set ws = Worksheets(2)
ws.Cells.ClearContents
Application.ScreenUpdating = False
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For k = 1 To Len(Cells(i, 1))
str1 = StrConv(Mid(Cells(i, 1), k, 7), vbNarrow)
str2 = Mid(str1, 2, 4)
If IsNumeric(str2) And Mid(str1, 6, 2) = "年)" Then
With ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = str2
.Offset(, 1) = Cells(i, 1)
End With
End If
Next k
Next i
Application.ScreenUpdating = True
ws.Columns("A:B").Sort key1:=ws.Cells(1, 1), order1:=xlAscending
ws.Columns.AutoFit
End Sub 'この行まで

※　今後もデータが増えるというコトですので、Sheet1のデータ変更があるたびにマクロを実行してみてください。

参考になれば良いのですが・・・m(_ _)m

お礼

みなさま、レスありがとうございます。

補足ですが”(”（半角）及び”)”（半角）は1行のデータ内には
西暦の前後以外には、存在しません。

それと、こちらで、データーを改ざんしてデータの形式を変更してしまいました。
（色々、データを修正してるうちに、誤って最初のデータを削除してしまいました。
　修正前のデータは別のPCに残っているのですが....手元にありません。)

誠にすいませんが、下記で検討お願いします。

西暦は、必ず最後になります。

例：
　　山本薩夫監督による『忍びの者』(1962)
となります。
　　（”年”の文字は、データから削除しました。）

”(”の後に４桁の西暦番号で最後に”）”です。

勝手なお願いですが、フォローお願いします。
-----------

返事を書いている間に回答をいただきました。

VBAを試してみたいと思います。
検証に時間を戴けると幸いです。

データを改ざんしてしまったの提示頂いたでマクロで不具合があればフォローお願いします。
（VBAも一応、操作できますが”マクロ使い”でないので詳しい内容になると？？です。）

回答No.4

shintaro-2

いろいろなやり方があるとは思いますが、

たとえばB列にデータがあるとして、
必ず「年」という文字の前に西暦が4桁で入っているのですから、
他に「年」という文字がないという前提がつきますが、

=mid(b1,(find("年",b1)-4）,4)　　-4で良いのかどうかちょっと怪しいですがそこは調整してください。

で年を切り出せないでしょうか？

お礼

みなさま、レスありがとうございます。

補足ですが”(”（半角）及び”)”（半角）は1行のデータ内には
西暦の前後以外には、存在しません。

それと、こちらで、データーを改ざんしてデータの形式を変更してしまいました。
（色々、データを修正してるうちに、誤って最初のデータを削除してしまいました。
　修正前のデータは別のPCに残っているのですが....手元にありません。)

誠にすいませんが、下記で検討お願いします。

西暦は、必ず最後になります。

例：
　　山本薩夫監督による『忍びの者』(1962)
となります。
　　（”年”の文字は、データから削除しました。）

”(”の後に４桁の西暦番号で最後に”）”です。

勝手なお願いですが、フォローお願いします。

回答No.3

okormazd

「山本薩夫監督による『忍びの者』(1962年)は作品」
などのデータが入力されている列の前が空いていれば、
(下記例はB1からデータが入力されている場合です)
1.
その空いている前の列(例はA1)に
=MID(B1,FIND("年",B1)-4,4)
と入れて、下にフィルドラッグする。
2.
A列とB列を選択して、「並べ替え」をすればいい。

列が空いていなければ、列を挿入する。

お礼

みなさま、レスありがとうございます。

補足ですが”(”（半角）及び”)”（半角）は1行のデータ内には
西暦の前後以外には、存在しません。

それと、こちらで、データーを改ざんしてデータの形式を変更してしまいました。
（色々、データを修正してるうちに、誤って最初のデータを削除してしまいました。
　修正前のデータは別のPCに残っているのですが....手元にありません。)

誠にすいませんが、下記で検討お願いします。

西暦は、必ず最後になります。

例：
　　山本薩夫監督による『忍びの者』(1962)
となります。
　　（”年”の文字は、データから削除しました。）

”(”の後に４桁の西暦番号で最後に”）”です。

勝手なお願いですが、フォローお願いします。

回答No.2

mshr1962

西暦4桁限定での抽出なら、下記の数式で可能です。
A2=MID(B2,FIND("年)",B2)-4,4)*1

お礼

みなさま、レスありがとうございます。

補足ですが”(”（半角）及び”)”（半角）は1行のデータ内には
西暦の前後以外には、存在しません。

それと、こちらで、データーを改ざんしてデータの形式を変更してしまいました。
（色々、データを修正してるうちに、誤って最初のデータを削除してしまいました。
　修正前のデータは別のPCに残っているのですが....手元にありません。)

誠にすいませんが、下記で検討お願いします。

西暦は、必ず最後になります。

例：
　　山本薩夫監督による『忍びの者』(1962)
となります。
　　（”年”の文字は、データから削除しました。）

”(”の後に４桁の西暦番号で最後に”）”です。

勝手なお願いですが、フォローお願いします。

回答No.1

m_and_dmp

他に「 ( 」がなければ　
=Mid(「セル参照」,Find(")",「セル参照」.1)+1,3)
で西暦の4ケタの数字を抽出することができますので、オリジナルデータが入力された列の一つ前の列に西暦を抜き出した数字を並べ、その列とオリジナルの列のデータがある範囲を選択してユーザー設定のソート（キーを「西暦の数字として」をかければご希望のようになると思います。

お礼

みなさま、レスありがとうございます。

補足ですが”(”（半角）及び”)”（半角）は1行のデータ内には
西暦の前後以外には、存在しません。

それと、こちらで、データーを改ざんしてデータの形式を変更してしまいました。
（色々、データを修正してるうちに、誤って最初のデータを削除してしまいました。
　修正前のデータは別のPCに残っているのですが....手元にありません。)

誠にすいませんが、下記で検討お願いします。

西暦は、必ず最後になります。

例：
　　山本薩夫監督による『忍びの者』(1962)
となります。
　　（”年”の文字は、データから削除しました。）

”(”の後に４桁の西暦番号で最後に”）”です。

勝手なお願いですが、フォローお願いします。