数値データの最後尾の値から判定し一括変換する

質問

「EXCEL」または「ACCESS」でのデータ変換について質問です。
下記のような「0」と「1」からなる10桁の数値があり、
値の末尾からみて数値を以下のように括変換する方法があれば教えて下さい。
（本来は10桁の数値なのですが、EXCELの仕様で先頭の「0」は非表示）

例(1）0　　　　　 →0
例(2）1111111111　→1|2|3|4|5|6|7|8|9|10
例(3）1010110000　→1|3|5|6
例(4）110010　　　→5|6|9
例(5）1000000010　→1|9
例(6）1　　　　　 →10

10桁の数値の左側から順に「1」になっていれば、その桁の数値に置き換える。（末尾１桁目は「10」、末尾３桁目は「7」、末尾10桁目は「1」）
また置き換えた数値が2個以上ある場合は、「|」の記号で区切る。
上記のような数値データが数万個あり、それらを一括で変換できる方法があれば教えて頂けると助かります。

お忙しい所 大変恐縮ではございますが、何卒宜しくお願い致します。

回答No.3

KURUMITO · ベストアンサー

回答Ｎｏ1です。
作業列を作らなくとも解決はできるのですが、式がかなり複雑になりますので示さなかったのです。
作業列で使ったREPT("0",10-LEN(A1))&A1の部分はＣ列で使うＢ1セルに対応しています。Ｃ列での式のうちで最初のＢ１はA1に変えればよいのですが、次からのＢ１はすべてREPT("0",10-LEN(A1))&A1で置き換えることにします。

お礼

KURUMITO様、ご回答ありがとうございます。
Ｂ１を「REPT("0",10-LEN(A1))&A1」に置き換えることで、希望通りの結果を1回で得られ、お蔭様で、作業効率がぐんとＵＰすることが出来ました。
本当に重ね重ね、お世話になりありがとうございます。

回答No.2

cistronezk

「1|9」のように最後に「|」を表示しないようにするには、たとえ作業列を使っても関数で対応すると非常に冗長になってしまいます。間違いが入り込み易いですし、間違があってもとても探しにくいです。

前回の回答で示されたユーザ定義関数に「TEXT(A1,"0000000000")」を渡すのが簡明だと思います。

お礼

ご回答いただき　ありがとうございます。
早速、参考にさせていただきます。

回答No.1

KURUMITO

前回のご質問では10桁の文字列の場合でしたね。
今回のご質問もＡ列にデータがあるとしたらＢ列を作業列としてＢ1セルには次の式を入力します。
=IF(A1="","",REPT("0",10-LEN(A1))&A1)
このようにしてからＢ1セルをアクティブセルにしてコピーします。次に名前ボックスにB1:B30000のように入力しEnterします。範囲として選択されますのでCtrl+Vの操作をして、その後にEscキーを押すことで、Ｂ1セルの式がB30000まで貼り付けられます。
Ｃ列には求める答えを表示することにします。
Ｃ1セルには次の式を入力します。
=IF(B1="","",IF(ISERROR(FIND(1,B1)),0,(IF(MID(B1,1,1)="1",1&"|","")&IF(MID(B1,2,1)="1",2&"|","")&IF(MID(B1,3,1)="1",3&"|","")&IF(MID(B1,4,1)="1",4&"|","")&IF(MID(B1,5,1)="1",5&"|","")&IF(MID(B1,6,1)="1",6&"|","")&IF(MID(B1,7,1)="1",7&"|","")&IF(MID(B1,8,1)="1",8&"|","")&IF(MID(B1,9,1)="1",9&"|","")&IF(MID(B1,10,1)="1",10,""))))
Ｃ1セルからＣ30000までコピーして貼り付けの操作はＢ列の場合と同様にすればよいでしょう。

お礼

KURUMITO様、前回に引き続き ご回答頂きましてありがとうございます。
早速試したところ、理想的な結果を得ることが出来ました。
【=IF(A1="","",REPT("0",10-LEN(A1))&A1)】で、数値を逆に並べ替えてから、次に一括変換を行っているのですね!(^^)!

ちなみに、この式を１つにまとめることは可能でしょうか?
１つの数式にすると、かなり複雑なことになりそうでしたら可否だけでもお答えいただけると幸いでございます。

お忙しい所 大変恐縮ではございますが、宜しくお願い致します。

補足

ご回答いただいた数式から得られる結果が下記のようになりました。
例(1）0　　　　　 →0|
例(2）1111111111　→1|2|3|4|5|6|7|8|9|10|
例(3）1010110000　→1|3|5|6|
例(4）110010　　　→5|6|9|
例(5）1000000010　→1|9|
例(6）1　　　　　 →10|

この右端に表示される「|」を削除したいのですが、どうすればよろしいでしょうか?