イノベーション エンジニアブログ


株式会社イノベーションのエンジニアたちの技術系ブログです。ITトレンド・List Finderの開発をベースに、業務外での技術研究などもブログとして発信していってます!


このエントリーをはてなブックマークに追加

GoogleSpreadSheetで文字列から数字のみを抽出する方法のひとつ

困ったこと/したいこと

数値の分析とかしたいのに元データが文字列で表現されている!
このままだと加工できない!!
数値だけ抽出したい!!

そんなことありますか?ありませんか?・・・そうですか

今回のデータ

Excelか何かのデータをインポートしたてみたいな感じ!

gss orgdata.png

アウトプットイメージ

簡素!

gss outputimage.png

E列の数値を出すのが今回のメインです!

今回E列に書いた式

=LOOKUP(10^18,ARRAYFORMULA(LEFT(対象文字列,COLUMN($1:$1))*1))

解説

COLUMN($1:$1)

数値だけの配列をセットすることができます。
1,2,3,4といった並びです。
見かけ上は「1」と表示されます。

ただ単に昇順の数値配列が欲しいだけなので、 「$1:$1」じゃなくても「A1:Z1」とかでも良いです!

LEFT(対象文字列,COLUMN($1:$1))

対象文字列を左から切り出して配列化します。
この時点では文字列です。

LEFT(対象文字列,COLUMN($1:$1))*1

文字列に対して1をかけることで数値に転換します。
この時数値に直せないあたいは「#VALUE!」で表示されます。

これらをARRAYFORMULAを使い、 配列を横に展開した形で表現したものが下記画像です。
(※分かりやすように例の文字列を1000万人にしてみています!)

gss arrayex.png

続きです!

=ARRAYFORMULA(LEFT(対象文字列,COLUMN($1:$1))*1)

ARRAYFORMULAを使いLEFTで取得した内容を配列データのまま扱います。

=LOOKUP(10^18,対象配列))

対象配列から検索値の近似値を返します。その性質を利用して最大値を取得します。
今回適当に10^18(百京)としているので百京まではいい感じの最大値が返ります。

これらをまとめて書くとこんな感じ!!

=LOOKUP(10^18,ARRAYFORMULA(LEFT(対象文字列,COLUMN($1:$1))*1))

う〜ん、読みにくい!

詰まったところ

ExcelとGoogleSpreadSheetで仕様が違い、
ARRAYFORMULAを噛ませないとデータを配列として扱えないようです。

それを知るまで最初の数値しか抽出できなくて詰まりました。

Excelだと

=LOOKUP(10^18,LEFT(対象文字列,COLUMN($1:$1))*1)

でそのままいけます。 こんな風に表示されてました。

gss excel.png

う〜ん、 奥が深・・・そう!!

こちらからは以上です。

参考にさせていただいたURL