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　などの関数をうまく組み合わせて、何とかならないかと思っているのですが、良いアイデアが思い浮かびません。

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

宜しくお願い致します。

回答No.10

suekun · ベストアンサー

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

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))

お礼

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

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

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

回答No.11

cafe_au_lait

中間の抜き出しです。

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))))))),))

回答No.9

suekun

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

=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桁目まで対応しますね。

回答No.8

suekun

対応する桁数に制限がありますが
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桁の英字を抜き出せば出来ますよね。

回答No.7

grumpy_the_dwarf

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

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

回答No.6

noname#140971

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

まず、ルールある書式を持つ製品番号に変換する関数を考えます。
例えば、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

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

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

回答No.5

imogasi

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

回答No.4

mshr1962

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

回答No.3

mshr1962

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

お礼

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

例）
AF2383DE
AF3456E

回答No.2

suekun

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

補足

説明不足ですみません。

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

回答No.1

pamsd

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