- ベストアンサー
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 などの関数をうまく組み合わせて、何とかならないかと思っているのですが、良いアイデアが思い浮かびません。 何か良い方法ありますでしょうか？ 宜しくお願い致します。
- liao
- お礼率36% (87/240)
- オフィス系ソフト
- 回答数11
- ありがとう数2
- みんなの回答 （11）
- 専門家の回答
質問者が選んだベストアンサー
ついでなので、中間の数字の抜き出し。 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))
その他の回答 (10)
- cafe_au_lait
- ベストアンサー率51% (143/276)
中間の抜き出しです。 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))))))),))
- suekun
- ベストアンサー率25% (369/1454)
一箇所設定間違いしてました。 =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桁目まで対応しますね。
- suekun
- ベストアンサー率25% (369/1454)
対応する桁数に制限がありますが 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桁の英字を抜き出せば出来ますよね。
- grumpy_the_dwarf
- ベストアンサー率48% (1628/3337)
どうしてもExcelでやりたかったら、VBA使ってやるしかないでしょ うね。Excelの関数では、「ハイフンもしくはアルファベットを検索 して欲しい」なんてことはできません。 私なら正規表現の使えるエディタかなにかで分離しちゃいますよ。 Macなんで常用しているのはJEditですが、製品番号の列をペースト しておいて、検索文字列を{[A-z]+}{[0-9]+}{\-?.*}、置換文字列を \1,\2,\3とすれば、きれいにカンマ区切りになります。1分もかかり ません。
数式で頭をひねるよりも、手っ取り早く、関数でも作ったらどうでしょうか？ まず、ルールある書式を持つ製品番号に変換する関数を考えます。 例えば、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
- imogasi
- ベストアンサー率27% (4737/17068)
＞条件をはっきりさせてください。 それによって難易度がガラッと変わる。 ハイフンのあるものはーで２つに分ける これは判るが 数字で終わるものはサフィックスなし、 英字が現れたら英字部分をサフィックスなし、 というルールでよいですか。 ・右から判断すること ・桁数が不定である ・英字と数字文字との変化を捉える。 ・英字は温帯部分にも現れる ので非常に関数では難しいように予想する。 VBAガ必要ではないか。
- mshr1962
- ベストアンサー率39% (7418/18948)
#3です。すみません。先頭の2桁を外すのを忘れてました。 B2=MID(A2,2,FIND("-",A2&"-")-3) C2=RIGHT(A2,LEN(A2)-LEN(B2)-2)
- mshr1962
- ベストアンサー率39% (7418/18948)
B2=LEFT(A2,FIND("-",A2&"-")-1) C2=RIGHT(A2,LEN(A2)-LEN(B2))
お礼
ご回答いただいた方法で「-」がサフィックスとの間に 含まれる場合はうまく出来そうですが、 ハイフンガ入らない場合、何にかよい方法ありますでしょうか？ 例） AF2383DE AF3456E
- suekun
- ベストアンサー率25% (369/1454)
数字がサフィックスである場合。 必ず「-」が間に存在すると考えてよろしいですか？ 英字の場合のみ、「-」無しで連結されていると。
補足
説明不足ですみません。 数字がサフィックスである場合は必ず「-」が間に存在します。 英字の場合のみの場合は「-」があったりなかったりします。
- pamsd
- ベストアンサー率18% (39/209)
区切る条件を 明確に 教えてください。
関連するQ&A
- VBA エクセル 文字列の桁数指定 先頭に"0"
お世話になります。 セルに入力されたコードが、指定された桁数未満であった場合に、足りない桁数分を調整するため先頭に"0"ゼロを追加したいと思っております。 具体的には、10桁に指定されたコード番号を、間違えて6桁で入力した場合、先頭に"0000"を追加して10桁に調整したいのです。 (1)誤：54321Q ↓調整 (2)正：000054321Q 入力したら瞬時に修正をさせたいので、WorksheetにChengeEventを利用して書こうと思っているのですが、下記のとおり入力された桁数が1~9桁であった場合のそれぞれの指定を書くことしか思いつかなくて。 もう少し、Simpleな書き方はないものでしょうか？ ～前略～ s = Cells(Target.Row, Target.Column).Value If s = "" Then Exit Sub End If i = Len(s) If i > 10 Then MsgBox "顧客番号(ICRIS#)は10桁です。" Exit Sub ElseIf i = 1 Then Cells(Target.Row, Target.Column).Value = "000000000" & s exitsub ElseIf i = 2 Then Cells(Target.Row, Target.Column).Value = "00000000" & s exitsub ～省略～ よろしくご指導くださいませ。
- ベストアンサー
- その他MS Office製品
- エクセルの並び替えの仕方を教えてください
質問番号：5465291で関数で文字分離ができずに困っていますと質問し、分離はうまくいきましたが、結果に対して並び替えが思うようにできません。どなたか教えてください。 回答は、 A1に値が入っているとして、 B1に =IF(ISERROR(FIND("-",A1)),A1,MID(A1,1,IF(FIND("-",A1)>0,FIND("-",A1)-1,LEN(A1)))) C1に =IF(ISERROR(FIND("-",A1)),"",MID(A1,IF(FIND("-",A1)>0,FIND("-",A1)+1,LEN(A1)),9999)) でした。 1 １２ ２ ２１１１ ３ という順番になってしまいます。
- 締切済み
- その他（ビジネス・キャリア）
- 全角・半角混在の文字列から半角文字のみ取り出す
エクセル勉強中です。問題集で理解できないところがあります。 数式がどういう意味をもつのか教えて頂けるとありがたいです。 よろしくお願いします。 画像添付の問題になります。 A列に製品名が入っています。（製品番号：半角文字）（製品名：全角文字） B列に半角文字の製品番号だけを取り出しなさいというものです。 半角文字の開始位置がバラバラになっているところが問題のポイントになっています。 回答ですが B2： =MID(A2,MATCH(1,INDEX(LENB(MID(A2,COLUMN(2:2),1))*1,0),),LEN(A2)*2-LENB(A2)) こちらで半角文字のみ取り出せるようです。回答には数式のみで何故この関数を使うのか？ 使うことでどういった結果を導くなどの解説が一切ありません。（ちなみに出版会社の便利技的な問題集です） MID関数で製品名A2から開始位置を指定して、全角半角をLEN関数LENB関数で半角文字数を 算出して文字列を抽出するという事は理解できます。 ただ、この開始位置の指定の所が理解できません。 数式を分割してみましたが =MID(A2,COLUMN(2:2),1)の所はどの行も製品名の１文字目ですよね・・・ その値にLENB関数で文字数？ 数式の検証で見てみると配列のような結果が次々と現れて・・・ MATCH関数もありますしINDEX関数が何か関係しているような気はしているのですが、 INDEX関数と言えば配列に行番号・列番号と例えば表の該当するセルの位置抽出の 知識しかありません。一つのセルでINDEX関数？ すいません。独学で勉強していてこの程度の知識ですが、この数式の考え方教えてくださる方よろしくお願いします。
- ベストアンサー
- Excel（エクセル）
- 32進数の文字に＋1カウントした値の取得
Access(vba)で32進数の値に＋１カウントした結果を取得したいと考えています。 引数、戻り値とも32進数の値の独自関数を作りたいです。 例） 11AF00 ⇒ 11AF01 11AF0Z ⇒ 11AF10 11AFZZ ⇒ 11AG00 下記のコードを途中まで考えましたが、繰り上がりとか考えてると？？？になってしまいました。 どなたかご教授お願いします。 Public Function P36進数カウント(str As String) Const alpha = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ" Dim i As Integer Dim keta As Integer keta = Len(str) ←桁数分LOOPするとか考えたのですが、ギブアップです。 Mid(str, i, 1) = Mid(alpha, InStr(alpha, Mid(str, i, 1)) + 1, 1) End Function
- ベストアンサー
- Access（アクセス）
- EXCEL 文字列分割について
EXCEL2003を使用しています。 1つのセルに文字列が入力されており、 それを決まったバイト数ずつ別のセルに分割したいと考えています。 例）A1の文字列を6バイトずつ分割したい A1:東京都文京区 ↓ B1:東京都 C1:文京区 これを実現する為に B1に「=LEFTB(A1,6)」 C1に「=MIDB(A1,7,6)」という数式を入力しました。 A1の文字列が全角のみ（または半角のみ）なら 上記の数式で問題ないと思うのですが、 分割対象の文字列は全角と半角が混ざっており、 例えばA1の値が 「a東京都文京区」 というようなものだった場合、 B1:a東京 C1:文京 となり、元の文字列にある「都」の文字が消えてしまいます。 元の文字列が上記の様な場合には B1:a東京 C1:都文京 というように値が返されるようにしたいのですが、 どのように実現したらよいでしょうか？
- ベストアンサー
- その他MS Office製品
- C言語 初心者です。
いつもお世話になっています。 またまた困ってます。 以下のソースは一部分を抜粋したものです。 質問内容ですが、現在コンパイルをすると、warningが出てきて、型の互換性がありません。とでてきます。たぶん文字列の長さを読むときがおかしいみたいで、型を色々変えましたが、warningはとれません。どうしたらいいか教えてください。 ちなみに #define NAME 128 /* IDの桁数 */ typedef struct { char name[NAME]; /* 社員番号 */ } syain; size_t str_length(char *str) { size_t length = 0; while (*str++){ length++; } return(length); } void namecheck(syain data[], int syain_num) { int len = 0;/* 社員名の桁数 */ while(1){ printf("社員名:"); scanf("%s", data[syain_num].name); /* 社員名の桁数を変数lenに格納する */ len =(unsigned)str_length(data[syain_num].name); putchar('\n'); if (len < NAME){ break; } } } void syaintouroku(syain data[], int syain_num) { namecheck(data[syain_num].name, syain_num); putchar('\n'); } int main(void) { syain meibo[MAX_NUMBER]; int max_syain = 0; int i; syaintouroku(meibo, max_syain); max_syain++; return (0); }
- ベストアンサー
- C・C++・C#
- A-ONEラベル屋さん連番文字列の作成
よろしくお願いします。 A-ONEラベル屋さん連番文字列の作成がうまくできません。 掲載されている操作手順の通りに実施しました。 ＯＫボタンをクリックして、セル？にNo.は振られているのですが、用紙に反映されず、 印刷しても印字されません。 ＯＫした後、何か操作が必要でしょうか？ ご存知の方、教えてください。 ●操作手順 はじめに連番文字を挿入開始するセルを選択します。 このとき作成する複数行セルを選択すれば、作成する数の初期値に使えます。 複数列選択時には使えません。 [リンク機能]-[連番文字の作成]メニューで[連番文字の作成]ダイアログを表示します。 [作成枚数]には選択時の行数が入っています。作成する枚数を入力してください。 [最初の番号]や[先頭文字列]/[末尾文字列]、[桁数]・[増分]を指定します。 準備ができたら[OK]ボタンをクリックします。 連番文字が作成されるセル上にある文字は置き換えられて元には戻せません。 確認ダイアログが表示されます。 確認の上[OK]ボタンをクリックします。
- ベストアンサー
- その他(ソフトウェア)
- エクセルで文字列をスペースで列に分割
エクセルマクロ超初心者です。 住所録を作成しているのですが、既存データの形式が、G列に郵便番号と住所がスペースを挟んで同一セルに入っています。 コレを、何かボタンを押したら郵便番号と住所を別々の列に分割するようなマクロを教えて頂きたいのですが。色々本で調べているのですが思うように進みません。 教えていただけると非常に助かります。
- 締切済み
- オフィス系ソフト
- 文字列の分割
"abcdefghijk"というような文字列を 例(1)：abcd、efg、h、i、jk 例(2)：ab、cdefg、h、ijk 例(3)：a、b、c、de、f、ghijk のように分割したいのですが、substringを使って取得するしかないでしょうか。 素人考えですみませんが、 class test{ char[] a = new char[4]; char[] b = new char[3]; char c; char d; char[] e = new char[2]; } のようなクラスを作り、 test t = new test(); t = "abcdefghijk"; などどすることで、分割する方法はないでしょうか。 ＃上記を試したところ、互換性のない型とエラーになりました。 分割するパターンが複数あり、文字列が長くなるとsubstringを記述する回数が増え、 終了インデックスを間違えそうで。 ＃間違えないようにすればいいだけの話ですが。 substring以外の方法、またはsubstringの上手な使い方がありましたら、教えていただけないでしょうか。 よろしくお願いします。
- ベストアンサー
- Java
お礼
ありがとうございます。 皆さんから頂いた方法にて出来そうです。 VBAを使うほどでもなくExcelの式で出来そうな気がして （私が式を知らないだけで）質問させて頂きましたが あまく見ていました。 VBAでやったほうが良かったかもしれません。 問題解決できました。 ありがとうございます。