04 Feb 2019 • on ozasa, GoogleSpreadSheet, Excel

by yuki ozasa

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

困ったこと/したいこと

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

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

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

簡素！

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万人にしてみています！)

続きです！

=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を噛ませないとデータを配列として扱えないようです。

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

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

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

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

こちらからは以上です。

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