COLUMN（１：１）の意味を教えてください

質問

こんにちは。
OSはWindowsXP、Excelは2003を使用している者です。

電話番号からハイフンをとりたいのですが、電話番号の末尾についたハイフンだけは取りたくないのです。末尾のハイフンはいくつつくかわかりません。
関数でそれを実現したいと思っています。
例えば、ExcelのA1セルに

03-1234-5678---

という電話番号があるとします。
関数の結果としては、

0312345678---

という結果を返してほしいのです。
そこで、ネットでいろいろ調べて以下の関数を作るに至りました。

=SUBSTITUTE(A1,"-","")&RIGHT(A1,MATCH("*"&RIGHT(SUBSTITUTE(A1,"-",""),1)&"*",INDEX(RIGHT(A15,COLUMN(1:1)),),0)-1)

03-1234-5678---のハイフンを一度全部取り（前半のSUBSTITUTE(A1,"-","")部分）、その後一番右側にあるハイフンと合体させています。

実は、前半のSUBSTITUTE(A1,"-","")部分以外は全く自分ではわからず、似た様な関数が掲載されていたネット部分を修正して作りました。
よって、どうしても意味不明な箇所があります。

COLUMN（１：１）はどういう意味なのでしょうか？
1～256の配列を返しているという風に言っている人がいましたが、それの意味が理解できません。

また、上の例で言うとRIGHT(A1,COLUMN(1:1))の部分では「8」を返すようなのですが、どうしてでしょうか。
COLUMN(1:1)＝1になるみたいなのでRIGHT（A1,1）としてみるとエラーになってしまいます。
なぜCOLUMN(1:1)だとうまくいくのに、1だとダメなのでしょうか。
そもそも、COLUMN(1:1)というのはどういうことなのでしょうか。

大変お手数ですが、どうしてもわかりません。
どなたかお詳しい方、教えていただけますでしょうか。
よろしくお願いいたします。

回答No.2

MackyNo1 · ベストアンサー

>実は、前半のSUBSTITUTE(A1,"-","")部分以外は全く自分ではわからず、似た様な関数が掲載されていたネット部分を修正して作りました。

>COLUMN（１：１）はどういう意味なのでしょうか？

エクセル２００３までなら、COLUMN（１：１）は１行目の列番号の配列、すなわち１から２５６までの連続した数字（配列）を返します。
ちなみに最大文字数がもっと短いならCOLUMN(A1：P1)などに変更することも可能です（この方が計算負荷が少ない）。

数式の後半部分を説明すると、

INDEX(RIGHT(A15,COLUMN(1:1)),)の部分は

RIGHT関数でA15セル（A1セルの間違い？）の文字列の右から1文字、2文字、・・・、256文字（文字数が満たない場合は最大文字数）を切り取った文字列の配列を取得し、その配列をINDEX関数で範囲に変換しています（Ctrl+Shift+Enterで確定するならINDEX関数は不要）。

例えば、「0312345678---」なら、「-」「--」「---」「8----」「78---」・・・と続く配列になります。

"*"&RIGHT(SUBSTITUTE(A1,"-",""),1)&"*"の部分は

A1セルから「‐」を削除した文字列の一番右の文字の前後にワイルドカード文字を挿入して、一番右の文字の前後に何か文字列が続いている（何もない場合もOK)文字列を示しています。

例えば、「0312345678---」なら、「*8*」になりますが、実際の数式では前方の文字列はMATCH関数で考慮する必要はないので前半の「"*"&」の部分は必要なく、「RIGHT(SUBSTITUTE(A1,"-",""),1)&"*"」の部分だけでOKです。

MATCH("*"&RIGHT(SUBSTITUTE(A1,"-",""),1)&"*",INDEX(RIGHT(A15,COLUMN(1:1)),),0)の部分は

MATCH関数は、上記のワイルドカード文字を含む検索値（すなわち最後のハイフンの前の文字列の前後に任意の文字列が続く）をRIGHT関数で元の文字列の右から1文字、2文字、・・・、256文字を切り取った文字列の配列の中から検索し、最初に見つかったデータの位置を求めることができます。

この位置は、最後のハイフンの前の数字ですので（8---が最初にヒット）、これから１を引いた数が最後のハイフンの数になるわけです。

お礼

MackyNo1さん、ありがとうございました。
お礼が遅れてすみません。

"*"&RIGHT(SUBSTITUTE(A1,"-",""),1)&"*"の部分は、最初の"*"&は不要なのですね。

＞ちなみに最大文字数がもっと短いならCOLUMN(A1：P1)などに変更することも可能です（この方が計算負荷が少ない）。

こちらも全くわかりませんでした。
最大文字数は実はわかりませんが、電話番号なので一番長そうな国際電話を考えてもそれほど長くはならないんかもしれません、
できそうならやってみます。

ありがとうございました。

補足

＞RIGHT関数でA15セル（A1セルの間違い？）

すみません。
そうですね。
A1の間違いでした。
ご指摘ありがとうございました。

回答No.5

某HN クロメート(Chromate)（@CoalTar）

#1です。回答が表示されないようなのでもう一度

かなりややこしい式ですね。
問題の部分
=RIGHT(A1,MATCH("*"&RIGHT(SUBSTITUTE(A1,"-",""),1)&"*",
INDEX(RIGHT(A1,COLUMN(1:1)),),0)-1)

1. RIGHT(SUBSTITUTE(A1,"-",""),1)
は いちばん右の数字 サンプルの場合8

2. "*"&RIGHT(SUBSTITUTE(A1,"-",""),1)&"*"
アスタリスクを前後に入れる(ワイルドカード)
"*8*"

3. RIGHT(A1,COLUMN(1:1))
右の1文字分を配列の１列目("-")、右2文字分を配列の2列目("--")・・・256列目("03-1234-5878---")
サンプルの配列は(配列定数で途中まで提示)
{"-","--","---","8---","78---","878---","5878---","-5878---","4-5878---","34-5878---","234-5878---","1234-5878---","-1234-5878---","3-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---"}

4. INDEX関数は 配列を認識させるためのもの
([Ctrl]+[Shft] ＋[Enter] で確定すればINDEX関数不要)

5. MATCH関数で、8を含んだものを探す。
　 4列めに最初に見つかるので 4
ここまで分かればよいでしょうか？

考え方は似ていますが
=SUBSTITUTE(A1,"-","")&RIGHT(A1,LEN(A1)+1-FIND("--",A1&"--"))
"--"を検索させ、右の"-"をくっつける。

お礼

CoalTarさん、再びありがとうございます。

震災でシステムがストップしてしまいまして、システムがようやく今週より稼動し始めました。
それまでにたまった仕事を今週ずっとやっていました。
今回質問させていただいた数式は、震災以前に「どうにかして」と言われていた課題の1つでした。

それで残業が続いたりした為、お礼が遅れて申し訳ございませんでした。
皆さんのおかげで理解出来たような気がいたします。
ありがとうございました。

回答No.4

mu2011

>なぜCOLUMN(1:1)だとうまくいくのに、1だとダメなのでしょうか。
>そもそも、COLUMN(1:1)というのはどういうことなのでしょうか。
　⇒INDEX関数の範囲として、RIGHT関数の文字数を１・２・３・・・で配列化とする工夫の為に使用しているから１では駄目です。
　　例えば、配列は、(1)"-",(2)"--",(3)"---",(4)"0---"・・・になり、検索値の"*0*"から
　　４が戻され、-1とする事で入力セルの末尾ハイフンの文字数の３が求まることになる。

　　電話番号のハイフンの構成バリエーションが色々あり且つ、末尾のハイフンの個数がランダムで
　　ある為、かなり練られた数式と思います。

お礼

mu2011さん、コメントありがとうございます。
お礼が遅れてすみませんでした。

＞⇒INDEX関数の範囲として、RIGHT関数の文字数を１・２・３・・・で配列化とする工夫の為に使用しているから１では駄目です。

そうなんですね。
COLUMN（1:1)が1を返しているからと、１にしてはだめなんですね。
配列の理解が乏しいのと、複数のセル範囲のみならず、1つのセルの中で文字を分割して配列化できるという発想がなかったので混乱していました。
皆さんが教えてくださったおかげで少しずつわかってきました。

ありがとうございました。

回答No.3

noname#204879

「末尾のハイフン」より左のハイフンが二つと決まっているなら、次式で如何かと。
=SUBSTITUTE(SUBSTITUTE(A1,"-","",1),"-","",1)

お礼

mike_gさん、コメントありがとうございました。
お礼が遅れましてすみませんでした。

実は末尾のハイフンより左のハイフンも2つとは限らないのです。
私も一瞬mike_gさんがおっしゃるような式でいいかなと思ったのですが、2つ以上のケースもたまにあることを思い出してしまいました。

ありがとうございました。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

かなりややこしい式ですね。
問題の部分
=RIGHT(A1,MATCH("*"&RIGHT(SUBSTITUTE(A1,"-",""),1)&"*",INDEX(RIGHT(A1,COLUMN(1:1)),),0)-1)

1. RIGHT(SUBSTITUTE(A1,"-",""),1)
は いちばん右の数字 サンプルの場合8
2. "*"&RIGHT(SUBSTITUTE(A1,"-",""),1)&"*"
アスタリスクを前後に入れる(ワイルドカード)
"*8*"
3. RIGHT(A1,COLUMN(1:1))
右の1文字分を配列の１列目("-")、右2文字分を配列の2列目("--")・・・256列目
サンプルの配列は(配列定数で途中まで)
{"-","--","---","8---","78---","878---","5878---","-5878---","4-5878---","34-5878---","234-5878---","1234-5878---","-1234-5878---","3-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---","03-1234-5878---"}
4. INDEX関数は 配列を認識させるためのもの
([Ctrl]+[Shft] ＋[Enter] で確定すればINDEX関数不要)
5. MATCH関数で、8を含んだものを探す。
　 4列めに最初に見つかるので 4

考え方は似ていますが
=SUBSTITUTE(A1,"-","")&RIGHT(A1,LEN(A1)+1-FIND("--",A1&"--"))
"--"を検索させる

お礼

CoalTarさん、回答ありがとうございました。
お礼が遅れてすみませんでした。
ご丁寧に回答していただき、とても感謝しています。
質問事項のみならず（こちらも知りたかったのですが）、[Ctrl]+[Shft] ＋[Enter] で確定した時についても教えていただき、ありがとうございます。

だいぶ関数の意味がわかるようになりました。
まだ配列の部分は何となくでしかありませんが、だいぶわかるようになった気がします。
ありがとうございました。