こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

Excelの抽出方法について

Excel2003において、英字のみと数字のみを抽出する方法を教えてください!(出来れば関数のみとVBAの両方)
A列にはaaaa1111のように英字+数字のパターンで500行ほどあり、
英字の文字数は決まっておらず、数字は3字か4字のどちらかになります。
B列に英字のみ、C列に数字のみの結果を出したいです。

ご教授の程よろしくお願いします。

投稿日時 - 2009-06-21 20:38:09

QNo.5063279

すぐに回答ほしいです

質問者が選んだベストアンサー

こんばんは。
>(出来れば関数のみとVBAの両方)

質問は片方のみにしてください。VBAは、VBAの良さがありますし、関数は関数の良さがあります。ご質問者さんが、VBAが出来るなら、そちらの方がよいけれども、分からないままに使うものだとは思いません。
なお、関数も、VBAも、数字と文字を選り分けるものではなく、二分させるものです。現行では、数字は、半角のみです。


B列
=MID(A1,1,MATCH(TRUE,INDEX(ISNUMBER(MID(A1,ROW(A1:A255),1)*1),,),0)-1)

C列
=SUBSTITUTE(A1,B1,"")

---------------------------------------------
'コードの貼り付けは、標準モジュールが良い

Sub TestMacro1()
  Dim c As Variant
  Application.ScreenUpdating = False
  For Each c In Range("A1", Range("A65536").End(xlUp))
    If VarType(c.Value) = vbString Then
      c.Offset(, 1).Value = regRep(c.Value)
      c.Offset(, 2).Value = regRep(c.Value, True)
    End If
  Next c
  Application.ScreenUpdating = True
End Sub

Function regRep(ByVal txt As String, Optional flg As Boolean = False)
Dim Ptn As String
With CreateObject("VBscript.RegExp")
   If flg = False Then
     Ptn = "[^\d]+" '文字
   Else
     Ptn = "[\d]+" '数値
   End If
   .Pattern = Ptn
   .IgnoreCase = False
   .Global = True
   If .Test(txt) Then
    regRep = .Execute(txt).Item(0).Value
   End If
End With
End Function

投稿日時 - 2009-06-22 01:10:01

ANo.7

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(8)

ANo.8

関数で求める方法では次のような事でもよいでしょう。
B1セルには次の式を入力します。
=LEFT(A1,IF(ISNUMBER(VALUE(RIGHT(A1,4))),LEN(A1)-4,LEN(A1)-3))
C1セルには次の式を入力します。
=SUBSTITUTE(A1,B1,"")

投稿日時 - 2009-06-22 06:53:08

ANo.6

将来正規表現の入り口をEXCELで使いたい人が出たときのために、テストしたコードを挙げておきます。
Sub test03()
'Microsoft VBScript Regular Expressions 5.5を参照設定
Dim test As String
For i = 1 To 5
text = Cells(i, "A")
Dim reg, matches
' Set objFS = CreateObject("Scripting.FileSystemObject")
'正規表現
Dim objRegExp As RegExp
Set objRegExp = New RegExp
objRegExp.Pattern = "[0-9]"
objRegExp.IgnoreCase = True
objRegExp.Global = True
Set matches = objRegExp.Execute(text)
s = ""
For Each n In matches
s = s & n
Next
Cells(i, "B") = s
Next i
End Sub
ーーー
例データ
A列   B列
1234sdf1234
345sdfgh345
234wer234
2345ttt2345
12rtyu12
---
数字以外は
objRegExp.Pattern = "[0-9]"を
objRegExp.Pattern = "[^0-9]"に変える。
Cells(i, "B") = sをCells(i, "C") = sに変える。
結果 C列
sdf
sdfgh
wer
ttt
rtyu
===
数字と文字が混じらない(123bc23のようなのがない)と言う条件に乗ったやり方で有るので注意。

投稿日時 - 2009-06-21 22:44:39

ANo.5

こんばんは!
すでに色々回答は出ていますが・・・

他の方法の一例です。

A1セルからデータがあり、B列に数字以外・C列に数字を分割する場合は

まず数字だけをC列に抽出します。
C1セル =IF(A1="","",MID(A1,MATCH(TRUE,ISNUMBER(MID(A1,ROW($A$1:$A$15),1)*1),0),COUNT(MID(A1,ROW($A$1:$A$15),1)*1)))

(ここで $A$1:$A$15 となっているのはA1セル内の文字数が最大15文字まで対応できます。)

次にB1セル =IF(C1="","",SUBSTITUTE(A1,C1,""))
B1・C1セルを範囲指定してオートフィルで下へコピーします。

尚、数字が文字列に挟まれていても、C列には数字のみが抽出されますので
B列には数字以外の残りの文字が表示されます。

これで希望に近い形にならないでしょうか?

参考になれば幸いです。
どうも長々と失礼しました。m(__)m

投稿日時 - 2009-06-21 22:38:21

ANo.4

VBAのコードまで回答者に書いてくれという、質問の書き方は、丸投げで規約違反です。少しは自分で勉強のこと。
ーー
正規表現なんてFSOのRegexpが有るが、質問者の技量がわからない。
ーーー
1文字ずつ数字以外の出現を聞いていくしかないと思う。
Sub test01()
For i = 1 To 2
v = Cells(i, "A")
For j = 1 To Len(v)
If Not IsNumeric(Mid(v, j, 1)) Then
Cells(i, "B") = Mid(v, 1, j - 1)
Cells(i, "c") = Right(v, Len(v) - j + 1)
Exit For
Else
End If
Next j
Next i
End Sub
ーー
テスト例データ
A列  B列   C列
1234sdf1234sdf
345sdfgh345sdfgh
ーー
この質問に限っては、関数では、定石があって、
Googleで「関数 数字と文字列を区切る」などで照会すると
http://cgi.fuji.ne.jp/~fj2094/cgi-bin1/exqalounge.cgi?print+200706/07060056.txt
のbykinさんのようなのがそれ。
定石では有るが、EXCEL関数の高級技。

投稿日時 - 2009-06-21 21:57:40

ANo.3

ユーザー定義関数として

Function moji(y As String) '数字以外を抽出
 Dim x
 For x = 1 To Len(y)
  If Not Mid(y, x, 1) Like "#" Then moji = moji & Mid(y, x, 1)
 Next x
End Function

Function num(y As String) '数字を抽出
 Dim x
 For x = 1 To Len(y)
  If Mid(y, x, 1) Like "#" Then num = num & Mid(y, x, 1)
 Next x
End Function

A1に aaa111 として、=moji(A1) で、aaa と表示
A1に aaa111 として、=num(A1) で、111 と表示

投稿日時 - 2009-06-21 21:38:03

C1: =MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),99)
B1: =SUBSTITUTE(A1,C1,"")

投稿日時 - 2009-06-21 21:12:40

ANo.1

方法はいくつもありますが、例えばA1に「aaaa1111」があるとき
B1に
 =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)
C1に
 =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1))
で「aaaa」と「1111」が分離できます

VBAでももちろんできますが、汎用的には正規表現を使うことになります。少し難しくなりますよ。VBAがどこまで使えるか分からないので割愛します。(Instr関数を利用して力ずくでやる方法が簡単かも)

投稿日時 - 2009-06-21 20:49:33

あなたにオススメの質問