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

解決済みの質問

Excelの式 「AE87361-DS」のような文字列を分割させたい

AE87361-DS
AF2383-32
AF23444
AF2383DE

のような製品番号があります。

Excelの式等を用いて型番号とサフィックスの部分を列として分けたいと思っています。

例)
 製品番号   型番号   サフィックス
AE87361-DS   87361   -DS
AF2383-32    2383    -32
AF23444      23444   
AF2383DE      2383   DE

製品番号は、先頭に2桁の文字、型番号、末尾にサフィックス(付いてたり/付いていなかったり)、となっています。
型番号とサフィックスは桁数は個々に違います
またサフィックスは、"-"が付いたり付いていなかったりもあります。


find、len、mid などの関数をうまく組み合わせて、何とかならないかと思っているのですが、良いアイデアが思い浮かびません。

何か良い方法ありますでしょうか?

宜しくお願い致します。

投稿日時 - 2007-07-11 11:45:17

QNo.3157866

困ってます

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

ついでなので、中間の数字の抜き出し。

A2に元の文字列、先に提示した数式がC2セルにあるとして

=MID(A2,3,LEN(A2)-(LEN(C2)+2))

で抜き出せます。

さらに、7桁目以降に記号が発生する場合の設定。
11桁まで対応

=IF(ISNUMBER(VALUE(MID(A2,7,1))),IF(ISNUMBER(VALUE(MID(A2,8,1))),IF(ISNUMBER(VALUE(MID(A2,9,1))),IF(ISNUMBER(VALUE(MID(A2,10,1))),IF(ISNUMBER(VALUE(MID(A2,11,1))),"",RIGHT(A2,LEN(A2)-10)),RIGHT(A2,LEN(A2)-9)),RIGHT(A2,LEN(A2)-8)),RIGHT(A2,LEN(A2)-7)),RIGHT(A2,LEN(A2)-6))

投稿日時 - 2007-07-11 14:24:38

お礼

ありがとうございます。
皆さんから頂いた方法にて出来そうです。

VBAを使うほどでもなくExcelの式で出来そうな気がして
(私が式を知らないだけで)質問させて頂きましたが
あまく見ていました。
VBAでやったほうが良かったかもしれません。

問題解決できました。
ありがとうございます。

投稿日時 - 2007-07-11 15:37:28

ANo.10

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

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

回答(11)

ANo.11

中間の抜き出しです。

B2:=MAX(INDEX(VALUE(0&MID(A2,3,ROW($A$1:$A$50)*NOT(ISERROR(VALUE(0&MID(A2,3,ROW($A$1:$A$50))))))),))

投稿日時 - 2007-07-11 14:45:16

ANo.9

一箇所設定間違いしてました。

=IF(ISNUMBER(VALUE(MID(A2,6,1))),IF(ISNUMBER(VALUE(MID(A2,7,1))),IF(ISNUMBER(VALUE(MID(A2,8,1))),IF(ISNUMBER(VALUE(MID(A2,9,1))),IF(ISNUMBER(VALUE(MID(A2,10,1))),"",RIGHT(A2,LEN(A2)-9)),RIGHT(A2,LEN(A2)-8)),RIGHT(A2,LEN(A2)-7)),RIGHT(A2,LEN(A2)-6)),RIGHT(A2,LEN(A2)-5))

これで10桁目まで対応しますね。

投稿日時 - 2007-07-11 14:18:04

ANo.8

対応する桁数に制限がありますが
A2セルに対象の文字列があるとして

=IF(ISNUMBER(VALUE(MID(A2,6,1))),IF(ISNUMBER(VALUE(MID(A2,7,1))),IF(ISNUMBER(VALUE(MID(A2,8,1))),IF(ISNUMBER(VALUE(MID(A2,9,1))),IF(ISNUMBER(VALUE(MID(A2,9,1))),"",RIGHT(A2,LEN(A2)-9)),RIGHT(A2,LEN(A2)-8)),RIGHT(A2,LEN(A2)-7)),RIGHT(A2,LEN(A2)-6)),RIGHT(A2,LEN(A2)-5))

これでサフィックスは抜き出せます。
但し!! 現状は先頭から9桁目までに記号(-)もしくは英字が
発生しない場合は対応しません。
例:AE873612-DS ○ AE8736123-DS ×

サフィックスが発生するのが必ず7桁目以降であれば、数字を
変える事で10桁目までに記号もしくは英字が発生すれば
対応できる様にはなります。

中間の数字は、サフィックスと先頭の2桁の英字を抜き出せば出来ますよね。

投稿日時 - 2007-07-11 13:51:23

どうしてもExcelでやりたかったら、VBA使ってやるしかないでしょ
うね。Excelの関数では、「ハイフンもしくはアルファベットを検索
して欲しい」なんてことはできません。

私なら正規表現の使えるエディタかなにかで分離しちゃいますよ。
Macなんで常用しているのはJEditですが、製品番号の列をペースト
しておいて、検索文字列を{[A-z]+}{[0-9]+}{\-?.*}、置換文字列を
\1,\2,\3とすれば、きれいにカンマ区切りになります。1分もかかり
ません。

投稿日時 - 2007-07-11 13:47:06

数式で頭をひねるよりも、手っ取り早く、関数でも作ったらどうでしょうか?

まず、ルールある書式を持つ製品番号に変換する関数を考えます。
例えば、AktSuffix()。

[イミディエイト]
? AltSuffix("AE87361-DS")
AE87361-DS
? AltSuffix("AF2383-32")
AF2383-32
? AltSuffix("AF23444")
AF23444
? AltSuffix("AF2383DE")
AF2383-DE

こうなれば、文字列を分割する関数が使えます。

? CutStr(AltSuffix("AE87361-DS"), "-", 1)
AE87361
? CutStr(AltSuffix("AE87361-DS"), "-", 2)
DS
? CutStr(AltSuffix("AF2383DE"), "-", 1)
AF2383
? CutStr(AltSuffix("AF2383DE"), "-", 2)
DE

この2段ステップを踏まないと、汎用性のないその場限りの関数に四苦八苦することにも。

Excel の数式では、次のように書けばいいです。

=MID(Cutstr(altsuffix("AE87361-DS"), "-", 1),3,99)-----> 87361
=Cutstr(altsuffix("AE87361-DS"), "-", 2)-------------> DS

Public Function AltSuffix(ByVal Text As String) As String
  Dim I As Integer
  Dim L As Integer
  
  If InStr(1, Text, "-", vbTextCompare) = 0 And _
    InStr(1, "0123456789", Right$(Text, 1), vbTextCompare) = 0 Then
    ' ----------------------------------
    ' '-' がない時は、数字を後から探す
    ' 末尾が数字であれば探さない
    ' ----------------------------------
    L = Len(Text)
    For I = L To 1 Step -1
      If InStr(1, "0123456789", Mid$(Text, I, 1), vbTextCompare) > 0 Then
        Text = Left$(Text, I) & "-" & Right$(Text, L - I)
        Exit For
      End If
    Next I
  End If
  AltSuffix = Text
End Function

Public Function CutStr(ByVal Text As String, _
            ByVal Separator As String, _
            ByVal N As Integer) As String
  Dim strDatas() As String
  
  strDatas = Split("" & Separator & Text, Separator, , 0)
  CutStr = strDatas(N * Abs((N <= UBound(strDatas))))
End Function

投稿日時 - 2007-07-11 13:29:01

ANo.5

>条件をはっきりさせてください。
それによって難易度がガラッと変わる。
ハイフンのあるものはーで2つに分ける
これは判るが
数字で終わるものはサフィックスなし、
英字が現れたら英字部分をサフィックスなし、
というルールでよいですか。
・右から判断すること
・桁数が不定である
・英字と数字文字との変化を捉える。
・英字は温帯部分にも現れる
ので非常に関数では難しいように予想する。
VBAガ必要ではないか。

投稿日時 - 2007-07-11 13:01:10

ANo.4

#3です。すみません。先頭の2桁を外すのを忘れてました。
B2=MID(A2,2,FIND("-",A2&"-")-3)
C2=RIGHT(A2,LEN(A2)-LEN(B2)-2)

投稿日時 - 2007-07-11 12:45:59

ANo.3

B2=LEFT(A2,FIND("-",A2&"-")-1)
C2=RIGHT(A2,LEN(A2)-LEN(B2))

投稿日時 - 2007-07-11 12:17:01

お礼

ご回答いただいた方法で「-」がサフィックスとの間に
含まれる場合はうまく出来そうですが、
ハイフンガ入らない場合、何にかよい方法ありますでしょうか?

例)
AF2383DE
AF3456E

投稿日時 - 2007-07-11 12:53:25

ANo.2

数字がサフィックスである場合。
必ず「-」が間に存在すると考えてよろしいですか?
英字の場合のみ、「-」無しで連結されていると。

投稿日時 - 2007-07-11 12:16:09

補足

説明不足ですみません。

数字がサフィックスである場合は必ず「-」が間に存在します。
英字の場合のみの場合は「-」があったりなかったりします。

投稿日時 - 2007-07-11 12:49:26

ANo.1

区切る条件を 明確に 教えてください。

投稿日時 - 2007-07-11 12:04:21

あなたにオススメの質問