エクセル 条件で表示の仕方を変える

教えてください。
L4～L19セルにP1230～連番で表示されています。
（P1239の次からはP123A～P123Fとなっています）
（"P"の前に文字があったりもします）
N4～N19セルに
"ABC"とか"DE"とか"FG123"とか"HJKL/"とか
色んな文字を入力します。
例えば、
N4セルに"ABC"と入力すれば、
M4セルに"ABC1230"と表示
N5セルに"DE"と入力すれば
M5セルに"DE1230"と表示
N6セルに"FG123"と入力すれば、
M6セルに"FG123"と表示
N7セルに"HJKL/"と入力すれば、
M7セルに"HJKL"と表示
という風に、M列を計算式で表示させたいです。
10000行くらいあり、手で変換するのは大変なので
どうかご教示ください。
上記では分りづらいと思いますので画像を添付します。

microsoft365のエクセルを使用しています。
よろしくお願いいたします。

質問者からの補足コメント

• ありがとうございます。
  L列は必ず末尾に必ず数字があるというわけではありませんが、数字があるところもあります。というのは、P1239の次からはP123A,P123B,P123B,P123C,P123D,P123E,P123Fとなるからです。Pの前には色々な文字があったりなかったりします。
  N列は全く規則性はなく、文字や数字や文字+数字が入ります。
  よろしくお願いいたします。

  No.3の回答に寄せられた補足コメントです。 補足日時：2021/09/28 07:49

No.1 回答者： mygoonickname 回答日時： 2021/09/27 23:23

こんばんは。

変換元と、変換先の表を別のシートに一覧として作って、Vlookup関数で
　N列を元に、M列に関数を入れて表示が良いのでは？と思いました。

VLOOKUP関数で別シートを参照する方法
https://office-hack.com/excel/vlookup-another-sh …

No.2 回答者： 銀鱗 回答日時： 2021/09/28 03:26

ごめんなさい。

質問の例だけでは規則性を見いだせないのです。

いくつも任意のルール（条件）あるなら、それらをすべて網羅する必要があります。
全部、質問の「補足」に書いてください。
（”P1230" の ”P” はどうなるの？などの説明もしてください）
それができないのでしたら、一つずつ手作業で処理することになります。

たかが10000行です。5人くらいで分担すれば、1時間もかからないでしょう。

この回答へのお礼

ご指摘ありがとうございます。L列の数字より前のアルファベットは削除となります。（添付画像の内容だと”P”、”CP”、”AP”、”FP”、”DP”、”HP”等）N列はアルファベット+数字かアルファベットのみになります。数字のみのパターンは無いです。あとは添付画像以外のルールは特にありません。

お礼日時：2021/09/28 19:24

No.3 回答者： chonami 回答日時： 2021/09/28 05:51

数字があるかどうかの判定では、数字は必ず末尾につくのですか？

No.4 回答者： chonami 回答日時： 2021/09/28 08:29

いやえっと、N列に数字があるかどうかで結果が変わるんですよね？

だからN列について聞いているんですが…

>文字や数字や文字+数字

このような書き方ですと、数字は末尾(後半)のように受け取れますが違うならどのようなパターンなのか具体例を出してください。サンプルにはなかったので。後、数字のみのパターンもないようですが…？
考えられるパターンは全て書き出した方がいいですよ。

この回答へのお礼

ご指摘ありがとうございます。N列はアルファベット+数字かアルファベットのみになります。数字のみのパターンは無いです。

お礼日時：2021/09/28 19:13

No.5 ベストアンサー 回答者： fujillin 回答日時： 2021/09/28 10:26

こんにちは

条件の説明が、重複する個別例だけで系統だっておらず、全てのケースが網羅されていないので不明部分がありますけれど、説明にない部分は勝手に解釈しました。

少なくとも、以下で、ご提示の例と一致した結果にはなります。
（ただし、M5セルは除く）

ご提示のレイアウトで、M4セルに
=IF(IFERROR(FIND("/",N4),0),SUBSTITUTE(N4,"/",""),IF(IFERROR(AGGREGATE(15,6,FIND(ROW(A$1:A$10)-1,N4),1),0),N4,N4&IFERROR(MID(L4,AGGREGATE(15,6,FIND(ROW(A$1:A$10)-1,L4),1),LEN(L4)),"")))
を入力し、下方にフィルコピーではいかがでしょうか。

この回答へのお礼

完璧です。ありがとうございました。

お礼日時：2021/09/29 17:11

No.6 回答者： goomania 回答日時： 2021/09/28 18:20

No.5さんのご指摘のとおり、ご質問者が例示されたM5セルの内容は「DE1230」ではなく「DE1231」が正しいように思います。

この前提でM4セルに記述すべき数式は、No.5さんの回答でよいと思います。
しかし、No.5さんの回答はご質問者が説明された条件をそのまま数式化されておられるので、若干長めの数式になっている気もします。
少し短い以下のような数式も使えると思います。M4セルに記述し、下方向へコピーするということになります。

=IF(IFERROR(FIND("/",N4),0)+IF(ISERR(AGGREGATE(15,6,FIND(ROW($1:$10)-1,N4),1)),0,1),SUBSTITUTE(N4,"/",""),N4&RIGHT(L4,4))

この回答へのお礼

回答ありがとうございます。いい感じまで行けたのですが、L列の数字より前のアルファベットが消えませんでした。（添付画像だと”P”、”CP”、”AP”、”FP”、”DP”、”HP”等）私もこれで填まってしまい、今回質問を投稿しました。

お礼日時：2021/09/28 19:28

No.7 回答者： goomania 回答日時： 2021/09/28 19:58

No.6です。

ご質問者のお礼に
>L列の数字より前のアルファベットが消えませんでした。
>（添付画像だと”P”、”CP”、”AP”、”FP”、”DP”、”HP”等）
>私もこれで填まってしまい、今回質問を投稿しました。
との記述がありますが、意味が判りません。
当方の環境では添付画像のとおり、正しく処理されてる気がするのですが････。当方の勘違いがあるのでしょうか?

No.8 回答者： goomania 回答日時： 2021/09/28 20:15

No.7です。

連投失礼します。
前回回答の添付画像のN6セルは「FG1232」となっていますが、ご質問者の例示では「FG123」です。
これはL6セルの内容が「CP1232」であるため、N列に同じ数字のデータが登場した場合、何らかの問題を引き起こさないかチェックするため、敢えて例示とは異なる「FG1232」を入力しています。
つまり、N6セルが「FG123」のときM6セルが「FG123」となることは既にチェック済みです。
例示データと異なる事情を補足説明させていただきました。

この回答へのお礼

たくさん確認してくださってありがとうございます。
原因が分かりました。例のL列は5文字以上しか記載してなかったのですが、L列の文字数は不規則に変わります。2文字～7文字で色々ある事を伝えられていませんでした。こちらの不手際です。申し訳ありません。

お礼日時：2021/09/29 00:36

No.9 回答者： goomania 回答日時： 2021/09/29 02:11

ご質問者の当初の説明では

＞L4～L19セルにP1230～連番で表示されています。
という話と、添付画像の説明とを併せて、Ｌ列の末尾4文字は「16進数」(0～Ｆまで）と理解していたのですが、
お礼のコメントで
＞L列の文字数は不規則に変わります。2文字～7文字で色々ある事を伝えられていませんでした。
との補足がありました。
本当は、5～7文字の例は既にお示しいただいているので2～4文字の例をお示しいただけないと回答のしようがないのですが、
＞L列の数字より前のアルファベットが消えませんでした。
＞添付画像だと"P"､"CP"､"AP"､"FP"､"DP"､"HP"等）
ともおっしゃっているので、
勝手に、Ｌ列は
(1)数字は4桁に限らず3桁以下のこともある
(2)さらには、2文字のときは末尾の4桁数字がないときもある
ということではないかと推測して、数式を修正してみました。
M4セルに以下の数式を記述して、下方向へコピーでいかがでしょうか?

=IF(IFERROR(FIND("/",N4),0)+IF(ISERR(AGGREGATE(15,6,FIND(ROW($1:$10)-1,N4),1)),0,1),SUBSTITUTE(N4,"/",""),N4&RIGHT(L4,IFERROR(MATCH(AGGREGATE(14,6,HEX2DEC(RIGHT(L4,ROW($1:$4))),1),INDEX(HEX2DEC(RIGHT(L4,ROW($1:$4))),0),0),0)))

添付画像の20～22行目のセルの内容をご確認ください。

この回答へのお礼

完璧です。ありがとうございました。

お礼日時：2021/09/29 17:10

No.10 回答者： goomania 回答日時： 2021/09/29 02:57

No.6～9です。

連投失礼します。
No.9で回答した数式に不備があり、「L列が『P0001』という様に0がつくとき、M列では0が省略されてしまう」という問題が発生することが判明しました。
つきましては、No.9回答の数式は無かったものとし、不備数式をお詫び申し上げるとともに、M4に記述する数式を以下の数式に訂正いたします。誠に申し訳ありませんでした。

=IF(IFERROR(FIND("/",N4),0)+IF(ISERR(AGGREGATE(15,6,FIND(ROW($1:$10)-1,N4),1)),0,1),SUBSTITUTE(N4,"/",""),N4&RIGHT(L4,AGGREGATE(14,6,(1-ISERR(HEX2DEC(RIGHT(L4,ROW($1:$4)))))*ROW($1:$4),1)))

添付画像も修正いたしました。