[EXCEL]セル内の数値の取り出しができる関数

質問

簡単な質問ですみません。
あるセルに以下のデータが入っていて
「11111111」の部分のみを取り出す関数を教えてください。
「11111111」と「EEEEE」の間には空白は3つあります。
「AAAA BBBBBB CC D 11111111 EEEEE」

また、VLOOKUP関数で取り出す方法があれば教えてください。

回答No.5

zinchan · ベストアンサー

No.4の方のおっしゃるとおりです。具体的な制限条件があれば、数式を単純化できますが、あらゆる条件を想定すると、式が複雑・煩雑になり、メモリーも消費します。
　仮に、文字列の数字部分は、任意の数字が１カ所で任意に連続しており、その１カ所しか存在しないこと、数字部分のあとには、空白文字が３つ必ずあるという２つの前提の元で下記に示したく思います。
　[A1]に文字・数字の羅列された文字列があるとします。文字・数字は任意の文字・数字・文字数であるとします。
　それに対応するセル(例えば[B1]に、
=MIN(IF(ISERROR(SEARCH(1,A1,1))=TRUE,32767,SEARCH(1,A1,1)),IF(ISERROR(SEARCH(2,A1,1))=TRUE,32767,SEARCH(2,A1,1)),IF(ISERROR(SEARCH(3,A1,1))=TRUE,32767,SEARCH(3,A1,1)),IF(ISERROR(SEARCH(4,A1,1))=TRUE,32767,SEARCH(4,A1,1)),IF(ISERROR(SEARCH(5,A1,1))=TRUE,32767,SEARCH(5,A1,1)),IF(ISERROR(SEARCH(6,A1,1))=TRUE,32767,SEARCH(6,A1,1)),IF(ISERROR(SEARCH(7,A1,1))=TRUE,32767,SEARCH(7,A1,1)),IF(ISERROR(SEARCH(8,A1,1))=TRUE,32767,SEARCH(8,A1,1)),IF(ISERROR(SEARCH(9,A1,1))=TRUE,32767,SEARCH(9,A1,1)),IF(ISERROR(SEARCH(0,A1,1))=TRUE,32767,SEARCH(0,A1,1)))
という数式を入れておきます。
　これは、０～９までの任意の数字を見つけて、その先頭の数字の位置が、文字列の開始位置から何番目か調べる式です。なお、式中の32767は、MSエクセルでの１セルの最大入力可能文字数です。
　さらに、[C1]に、
=IF(B5=32767,0,SEARCH(" ",A5,1)-B5)
　(式中の" "には、空白文字が３つ入っている)と入力すると、[A1]の文字列の内、空白文字が３つ連続して入っている位置の先頭文字数を調べ、それに[B1]の値を減じた値を返します。
　以上によって、数字の位置の先頭位置と、数字の連続桁数が算出できます。
　最後に、[D1]に、
=MID(A1,B1,C1)
と入力することで、[A1]セルの数字の開始位置から、連続桁数分の文字(数字)を取り出すことができます。
　３行以上上記の式を入れた行を設定しておき、オートコンプリートをかけていれば、[b・C]列を非表示にしていても、[A]列に任意の文字を入力すれば、式を手動でコピーすることなく、自動的に抽出された値が[C]列に返ってきます。

　ご質問の内容を行うにあたり、このような文字列がいくつもあって、それらの数字部分だけをそれぞれ取り出したいのではないかと思いました。よって、別表を使ったVLOOKUP関数は、あまり適さないのではないかと考えます。
　もっと簡単な式を作成することは可能かとは思いますが、条件がわからないので、任意の文字による文字列でも対応可能なものを考えました。

回答No.4

imogasi

#1,#2のご解答の中にもでてきますが、下記の点をはっきりさせないと、根本的に解答が変わると思います。
（１）「１１１・・・」は、実際でも１で、１の連なりが８桁なんですか。同じ文字列が複数個続いているのを抜き出す例示で挙げたのですか。１の連なりが、８桁の場合も１０桁の場合も６桁の場合もあるのですか。
（２）先頭の１か始まる先頭からの文字位置は固定ですか
。場合によって変わるのですか。
（３）１・・・１の部分は数字文字で、英文字ではないのですね。

回答No.3

kamonohashi

>「11111111」の部分のみを取り出す関数

文字数が変わらないと仮定して、
まず右から16文字（スペース3個を含む）を取り出し、
次に左から8文字を取り出す方法ではどうでしょう。

=LEFT(RIGHT(A1,16),8)

> また、VLOOKUP関数で取り出す方法があれば教えてください。

検索値に「AAAA BBBBBB CC D 11111111 EEEEE」
をいれてやれば、「あるセル」を検索することは可能ですが、
その中から「11111111」を取り出すには別の関数を組み合わせる必要があります。

回答No.2

maruru01

こんにちは。maruru01です。

取り出す数字が1とは限らず、またいくつ連続しているかもわからない場合は次のように、複雑な式になります。

A1に元の文字列、B1に取り出す数字（この場合は1）とすると、

=MID(A1,FIND(" " & B1,A1,1)+1,FIND(B1 & " ",A1,1)-FIND(" "&1,A1,1))

ちなみに、この場合B1に1以外を入れると、ERRORが表示されるので、それを避ける（ERRORの場合は""）のならば、

=IF(ISERROR(MID(略)),"",MID(略))

となります。
それともう1つ、"11111111"の前後のスペースが全角だと取り出せません。

回答No.1

cole

はじめまして。
関数の組合せはどうでしょうか？
(取り出す関数があるかもしれないですが、私には見つけられないので)

あるセルを A1 と仮定して
=MID(A1,FIND(11111111,A1,1),8)

FIND関数は指定した文字列が対象セルの何文字目から
始まっているのか数値を返します。
それをMID関数の「開始位置」としました。

なお、文字(半角全角関係なく1文字と数える)単位ではなく、
バイト(半角)単位で文字列を数えたい場合は、
FINDB・MIDB関数を使ってください。