Excelの関数で(例）1.赤 2.青 ・・・の場合の数字だけの取り出し方

質問

Excelで
A1セルに次のように入ってます(例として）

1.赤 2.青 3.黄色 11.紺色　　←アンケートの設問のようなイメージです。数字のあとにピリオドが入っています。

このセルから、数字だけを抽出して、次から始まる（この場合B1）から
1　2　3　11　としたいのです。（1セルに1数字が条件）

この場合、左詰めでも構いませんし、
必要なセル分（この場合は11）に、1　2　3　このあとセルは空白で　11番目に11　こういう方法でも構いません。

あまり関数を知らないので、
=IF(COUNTIF($A1,"*1.*"),1,"")、=IF(COUNTIF($A1,"*2.*"),2,"")と横に書いて、それぞれのセルに1が含まれていたら1　2が含まれていたら2・・・としていきました。
そうしましたら、「1」　「11」の区別がつかないで、11だと、1のところにも11のところにも数字が入ってしまいます。
これは、11以上あるカテゴリの場合は、そうなってしまい、2と12なども2も12もついてしまいます。

この区別の仕方を教えてください。
それと、上記の場合、もっとやりやすい方法があれば教えてください。
よろしくお願いいたします。

回答No.6

merlionXX · ベストアンサー

uminosobaさん、うまくいってよかったですね。

> ちなみに、B1からの列、C1からの列データが対象だとしたら、Range("A1").Activate　をB1、C1などと変えればいいのでしょうか？

そう単純なものではないので、Range("A1").Activate　のA1を書き換えるだけでOKのようなコードに修正してみました。
以下は、C10セルを基点としていますが、これを変えれば右に必要な列数がありさえすればどこでもいいはずです。お試しください。

Sub test03()
Dim i As Integer, x As Integer, n As Integer, s As String
Range("C10").Activate
Do Until ActiveCell.Value = ""
x = Len(Trim(ActiveCell))
For i = 1 To x
If IsNumeric(Mid(Trim(ActiveCell), i, 1)) Then
s = s & Mid(Trim(ActiveCell), i, 1)
End If
If IsNumeric(Mid(Trim(ActiveCell), i, 1)) And IsNumeric(Mid(Trim(ActiveCell), i + 1, 1)) = False And Mid(Trim(ActiveCell), i + 1, 1) <> "." Then
s = ""
End If
If Mid(Trim(ActiveCell), i + 1, 1) = "." And s <> "" Then
n = n + 1
ActiveCell.Offset(0, n) = s
s = ""
End If
Next
ActiveCell.Offset(1).Activate
n = 0
Loop
End Sub

お礼

merlionXXさん、このたびは、いろいろと教えていただき、本当にありがとうございます。
なかなか試す時間が持てずに、お礼の返事が遅くなり、すみません。
さきほど、"C10"の箇所を自分のシートでデータが始まっているところに変更させてもらい、マクロ実行しましたら、無事に出来ました。
変換しなければいけない項目(列）は、20ぐらいあったのですが、
1シートずつ分けて同じセルから始まるようにしておりますので、これで助かります。
これは、本当に便利なことを教えていただき、今後こういう作業があった際には、いつも使わせてもらおうと思います。
数字が変換されていくところが見られるのも安心して実行できます。
本当に何度もありがとうございました。<(_ _)>

回答No.5

merlionXX

#4のmerlionXXです。
#4のコードでは、数値以外の後ろに　．があったり、拾わなくてもよい数字（後ろに　．がないもの）があった場合、それも拾うので訂正しました。

Sub test02()
Dim r As Long, i As Integer, x As Integer, n As Integer, s As String
Range("A1").Activate
r = 1
Do Until ActiveCell.Value = ""
x = Len(Trim(ActiveCell))
For i = 1 To x
If IsNumeric(Mid(Trim(ActiveCell), i, 1)) Then
s = s & Mid(Trim(ActiveCell), i, 1)
End If
If IsNumeric(Mid(Trim(ActiveCell), i, 1)) And IsNumeric(Mid(Trim(ActiveCell), i + 1, 1)) = False And Mid(Trim(ActiveCell), i + 1, 1) <> "." Then
s = ""
End If
If Mid(Trim(ActiveCell), i + 1, 1) = "." And s <> "" Then
n = n + 1
Cells(r, n + 1) = s
s = ""
End If
Next
ActiveCell.Offset(1).Activate
n = 0
r = r + 1
Loop
End Sub

お礼

訂正のほういただきまして、ありがとうございます。
No4にやり方を書いてくださったので、それも有り難かったです。
No5のようにやっていったら、無事に先頭の数字だけが、右のセルに1つずつ入っていきました。
ちなみに、B1からの列、C1からの列データが対象だとしたら、Range("A1").Activate　をB1、C1などと変えればいいのでしょうか？
でも、きっと、列ごとデータをA1に移動させていって、
別のシートに1列ずつコピーしていったほうがよさそうですね。
何はともあれ、これで解決するような気がします。
本当にありがとうございました。<(_ _)>

回答No.4

merlionXX

大量のデータがあるなら、ここはVBAでやった方が便利です。
A1から下へA列にデータが入っている場合、以下の手順をおためしください。

1.Alt+F11キーでVisualBasicEditorを呼び出します。

2.メニューから挿入、標準モジュールで出てきたコードウィンド（右側の白い広い部分）に以下のコードをコピペします。

Sub test01()
Dim r As Long, i As Integer, x As Integer, n As Integer, s As String
Range("A1").Activate
r = 1
Do Until ActiveCell.Value = ""
x = Len(Trim(ActiveCell))
For i = 1 To x
If IsNumeric(Mid(Trim(ActiveCell), i, 1)) Then
s = s & Mid(Trim(ActiveCell), i, 1)
End If
If Mid(Trim(ActiveCell), i + 1, 1) = "." Then
n = n + 1
Cells(r, n + 1) = Val(s)
s = ""
End If
Next
ActiveCell.Offset(1).Activate
n = 0
r = r + 1
Loop
End Sub

3.Alt+F11キーでワークシートへもどります.

4.メニューから、ツール、マクロ、マクロで出てきたマクロ名（test01）を選択して実行

失敗するといけないので必ずコピーをとってからやってみてください。
（￣ー￣）v

お礼

お礼のほう、遅くなりすみません。
ご回答いただき、ありがとうございました。<(_ _)>
さきほど実行させていただきました。
ペーストしたのは、訂正をいただいたほうです。
すごい！！
実行されていく様子も見守れるし、安心ですね。
データのほうは、A1（というか実際は、項目名とかも入っていたりするので、B4から始まってたりして、右の列へ何列も続いていくのですが、
A1にデータを移動させて、A1から開始させることも出来るので大丈夫です。
この方法でしたら、該当箇所を1列ずつ実行していって、確認しながら、というのもアリですね。
だいぶ、作業がラクになりそうです。ありがとうございます。<(_ _)>

回答No.3

imogasi

このタイプの課題は、関数を使うと神経を使うだけ無駄。
ーー
データー区切り位置　の活用が良いと思う。
（１）同行空きセルで=SUBSTITUTE(A1,"."," ")を入れる。
それでピリオドを１スペースに置き換える。式を下方向に複写。
全行がピリオドがスペースになる・
（２）（全行につき）形式を選択して張り付けー値、で式を消す
（３）（２）の１列だけを範囲指定し、
データー区切り位置ー次へースペースー完了で
1 赤 2 青 3 黄色 11 紺色　
のように（全行が）各列に分かれる。
（４）漢字の列をCTRLを押しながら指定し、編集ー削除
ーー
1 2 3 11
のような結果になる。

お礼

お答えいただきありがとうございます。<(_ _)>
順を追ってやっていったら、出来ました。
区切り位置は、私も、よく使用するのですが、
何分、データが多かったので、関数に頼ろうかと思った次第です。
実は、語句の中に英語のセンテンスがあるものがあって、
（例）1. good job 2. 赤 3. 青・・・　とかなっており、スペースで区切るとgood とjobで別れてしまいズレるので困りまして、質問に至りました。（上記の場合は、一度、半角を取ってしまえば解決するかとも思いますが）
何分、時間がなくてあせっていたので、記述しないですみません。

回答No.2

izmlz

=MID(LEFT($A1,FIND("#",SUBSTITUTE($A1,".","#",COLUMN(A1)))-1),FIND("#",SUBSTITUTE(" "&$A1," ","#",COLUMN(A1))),256)

↑をセルB1に入力して、右の列にコピー＆ペーストでいかがでしょう？数式中の"#"は入力データとして使用されない文字であればなんでも結構です。
エラー処理するのなら、↓の数式です。

=IF(COLUMN(A1)>LEN($A1)-LEN(SUBSTITUTE($A1,".","")),"",MID(LEFT($A1,FIND("#",SUBSTITUTE($A1,".","#",COLUMN(A1)))-1),FIND("#",SUBSTITUTE(" "&$A1," ","#",COLUMN(A1))),256))

参考URL：

http://www2.odn.ne.jp/excel/waza/function.html

補足

お答えいただきありがとうございます。
今、実際にやってみまして確かに出来ました！
エラー処理をするのならというほうの式をペーストしました。
で、例題の1.赤 2.青 3.黄色 11.紺色は出来たのですが、
よくよくシートをみると、1. 赤 2. 青 3. 黄色 11. 紺色 といったように、
1.［半角］赤［半角］・・・となって続いているものがあり、
.のあとに半角を入れてみたのですが、うまくいきませんでした。
ご教示いただけましたら幸いです。
よろしくお願いいたします。<(_ _)>

回答No.1

ahirudac

A1に「1.赤 2.青 3.黄色 11.紺色」と入っていて、その時にB1に「1」、C1に「2」・・・と入る様にしたいということですね。（2行使わないと式が長くなりそうなので）あまり綺麗なやり方ではないですが、下記の方法で出来るかと。

A2に「=FIND(" ",$A$1,1)」、B2に「=IFERROR(FIND(" ",$A$1,A2+1),"")」、C2以降はB2をコピー

B1に「=LEFT($A$1,FIND(".",$A$1)-1)」、C1に「=IFERROR(MID($A$1,A2+1,FIND(".",$A$1,A2)-A2-1),"")」、D1以降はC1をコピー

考え方としては" "の次から"."の前までを文字として切り出すという物です。「FIND」で" "と"."の位置を割り出して、「MID」で文字を切り出しています。「IFERROR」はエラーが出た場合に何も表示させない為に使っています。

他にうまいやり方があるかもしれませんが参考までm(__)m。

お礼

早々とお答えいただきありがとうございます。<(_ _)>
やってみたのですが、実際のシートは、かなり大量にデータがありまして
2行になってしまうと、私には混乱してしまいそうです。
ターゲットとするのが少しのセルだけではないので、なかなか難しいのですが、考え方としての意味合い「" "の次から"."の前までを文字として切り出す」は何となく掴めました。
私が書いた=IF(COUNTIF($A1,"*1.*"),1,"")でやっていって、　"1"と"11"が区別できる方法はないものでしょうか。。