先頭の数値、最後の数値を取り出す

公開日：2017-03-26　最終更新日：2019-04-23

先頭の数値、最後の数値を取り出す

数値と文字が混在した文字列から、数値だけを取り出します、

先頭の数値や、最後の数値だけを取り出す方法です。

A1セルに
1234abcd5678

このA1セルから、1234や5678を取り出します。

先頭の数値・・・1234

=LOOKUP(10^17,LEFT(A1,COLUMN($1:$1))*1)

COLUMN($1:$1)

{1,2,3,4,5,6,7,8,・・・}
このような配列が返されます。

LEFT(A1,COLUMN($1:$1))

配列となっているCOLUMN関数を引数にしているので、
{1,12,123,1234,1234a,1234ab,1234abc,・・・}
このような配列が返されます。

LEFT(A1,COLUMN($1:$1))*1

*1
この計算をすることで、数値以外はエラー値となります。
{1,12,123,1234,#VALUE,#VALUE,#VALUE,・・・}
このような配列になります。

LOOKUP(10^17,LEFT(A1,COLUMN($1:$1))*1)

10^17は、100000000000000000
LOOKUPは、配列から上の数値以下の最大値が取り出されます。

最初の数値がLEFTで取り出しているので、最後の数値はRIGHTで取り出します。
考え方は、全く同じです。

最後の数値・・・5678

=LOOKUP(10^17,RIGHT(A1,COLUMN($1:$1))*1)

COLUMN($1:$1)

{1,2,3,4,5,6,7,8,・・・}
このような配列が返されます。

RIGHT(A1,COLUMN($1:$1))

配列となっているCOLUMN関数を引数にしているので、
{8,78,678,5678,d5678,cd5678,bcd5678,・・・}
このような配列が返されます。

LEFT(A1,COLUMN($1:$1))*1

*1
この計算をすることで、数値以外はエラー値となります。
{8,78,678,5678,#VALUE,#VALUE,#VALUE,・・・}
このような配列になります。

LOOKUP(10^17,LEFT(A1,COLUMN($1:$1))*1)

10^17は、100000000000000000
LOOKUPは、配列から上の数値以下の最大値が取り出されます。

COLUMN($1:$1)、これは、ROW($A:$A)、でも同じです。
要は、{1,2,3,4,5,6,7,8,・・・}このような配列を作成出来ればよいです。
ただし、COLUMN($1:$1)やROW($A:$A)は、配列が大きくなってしまうので、
計算時間を考えれば、作成する配列の大きさを限定した方が良いでしょう。
COLUMN($A$1:$Z$1)
このように、取り出す文字列長を超える配列であればよいのです。

また、10^17は、結果として取り出される数値より大きければよいので、
999999999999
のような定数で指定しても構いません。
逆の言い方をすれば、この桁数以上の連続数値は取り出すことができません。

最後のLOOKUPは、配列内の最大値を取り出すだけなので、MAXでも良いように思えますが、
MAX自体は配列を扱えますが、このように作られた配列は扱えないようで上手くいきません。

同じテーマ「エクセル関数応用」の記事

VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
SUMIF関数の良くある間違い
 論理式とは条件式とは(IF関数,AND関数,OR関数)
先頭の数値、最後の数値を取り出す
最後の空白（や指定文字）以降の文字を取り出す
 SUMIFの間違いによるパフォーマンスの低下について
 数値範囲で表検索するVLOOKUP近似一致
 エクセルの日付と時刻のまとめ
 連続数値部分を取り出し記号で連結
 指数近似/対数近似/累乗近似（掲載順位とCTR）
ジャンプ・セル選択の応用（空欄を埋める、1行置きに挿入）

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
エクセル全般
エクセル関数応用
先頭の数値、最後の数値を取り出す

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

エクセル関数応用
先頭の数値、最後の数値を取り出す