エクセルで数字、文字列混在のセルから数字のみを抽出し、その合計をスマー

質問

エクセルで数字、文字列混在のセルから数字のみを抽出し、その合計をスマートに計算する方法を探しています。

現在は他のWEBサイトから数字を抽出する方法が探せたので、それを元に、こんな式を使って計算しています。
=SUM(LOOKUP(10^17,LEFT(L34,COLUMN(2:2))*1),LOOKUP(10^17,LEFT(N34,COLUMN(2:2))*1),LOOKUP(10^17,LEFT(P34,COLUMN(2:2))*1),LOOKUP(10^17,LEFT(R34,COLUMN(2:2))*1),LOOKUP(10^17,LEFT(T34,COLUMN(2:2))*1),LOOKUP(10^17,LEFT(V34,COLUMN(2:2))*1),LOOKUP(10^17,LEFT(X34,COLUMN(2:2))*1),LOOKUP(10^17,LEFT(Z34,COLUMN(2:2))*1),LOOKUP(10^17,LEFT(AB34,COLUMN(2:2))*1),LOOKUP(10^17,LEFT(AD34,COLUMN(2:2))*1),LOOKUP(10^17,LEFT(AF34,COLUMN(2:2))*1),LOOKUP(10^17,LEFT(AH34,COLUMN(2:2))*1),LOOKUP(10^17,LEFT(AJ34,COLUMN(2:2))*1),LOOKUP(10^17,LEFT(AL34,COLUMN(2:2))*1),LOOKUP(10^17,LEFT(AN34,COLUMN(2:2))*1),LOOKUP(10^17,LEFT(AP34,COLUMN(2:2))*1))

このLEFT（xx34,・・・）の部分をまとめてくくれれば式が単純になると思いますが、いい方法が探せません。

もしこれ以外で、スマートになる方法があればそれでも歓迎します。

ちなみにセルに入力されているデータの例としては「150(4年間)」、「2000（6年間）」などです。
この数字だけの部分を足したいのです。
データが膨大になりフォーマットを変えるのは至極大変なので上述のことを考えた次第です。

皆さんのお力でなんとか出来ればと思っています。
よろしくお願いします。

回答No.8

rukuku

こんにちは

関数で１度にやろうとせずに２ステップで行えば簡単です。
１．データ→区切り位置→カンマやタブ…→その他にレ点で(
としますと、(の前後で分かれます。こうなれば、
２．SUM関数で合計
でできます。

回答No.7

layy

計算用に
「150(4年間)」、「2000（6年間）」
を
「150」、「2000」
にする作業は考えていますか？。ここまでできれば合計は楽です。

nullへの置換の繰り返しです。
『(x』の１～９回置換と『年間）』の１回置換。
これならマクロ化して１回で済むかも・・・。

回答No.6

keithin

=SUMPRODUCT((0&LEFT(L34:AP34,FIND("(",L34:AP34&"(")-1))*(1-MOD(COLUMN(L34:AP34),2)))
など。

回答No.5

layy

＞算出したい列同士の間隔は一定でないですね？。
すみません。無視してください。
間に必ず１列（のみ）あり、列位置としては規則性ありました。

補足

ありがとうございます。

そうなんです。データ間が1列空いているんです。今現在そこが課題です。

回答No.4

daiku164

参考にもならないと思いますが、

その入っているセルは、頻繁に変更されますか?

少しで有ればこんな方法もありますよ

150(6年間)のセルで右クリック→セルの書式設定→表示形式でユーザー定義
サンプルの下種類(T)の所に、0"(6年間)"と書きこみます
150(6年間)の文字を消し150と入力すると、150(6年間)と表示され
さらに通常計算が出来るようになります

ただし4年間とか3年間とか他の数字になる場合は、
ユーザー定義を一個一個指定又は書き変えなければなりません

補足

ありがとうございます。データ数はそこそこあります。少し面倒ですが、これよりいい案が無い場合は採用させていただきます。

回答No.3

KURUMITO

ご質問はＬ３４セルからＡＰ34セルにそのようなデータが入っているとしたら次の式でその合計が表示されますね。

=SUMPRODUCT(LEFT(L34:AP34,FIND("(",L34:AP34)-1)*1)

補足

ありがとうございます。
試してみましたが、出来ませんでした。
今回のは少しややこしいことに、LとNにデータが入っている。いわゆるひとつ飛びの状態です。
その場合はどのような回避策がありますでしょうか？

回答No.2

layy

式がわかりにくいので、
=SUM(
LOOKUP(10^17,LEFT(L34,COLUMN(2:2))*1),
LOOKUP(10^17,LEFT(N34,COLUMN(2:2))*1),
LOOKUP(10^17,LEFT(P34,COLUMN(2:2))*1),
LOOKUP(10^17,LEFT(R34,COLUMN(2:2))*1),
LOOKUP(10^17,LEFT(T34,COLUMN(2:2))*1),
LOOKUP(10^17,LEFT(V34,COLUMN(2:2))*1),
LOOKUP(10^17,LEFT(X34,COLUMN(2:2))*1),
LOOKUP(10^17,LEFT(Z34,COLUMN(2:2))*1),
LOOKUP(10^17,LEFT(AB34,COLUMN(2:2))*1),
LOOKUP(10^17,LEFT(AD34,COLUMN(2:2))*1),
LOOKUP(10^17,LEFT(AF34,COLUMN(2:2))*1),
LOOKUP(10^17,LEFT(AH34,COLUMN(2:2))*1),
LOOKUP(10^17,LEFT(AJ34,COLUMN(2:2))*1),
LOOKUP(10^17,LEFT(AL34,COLUMN(2:2))*1),
LOOKUP(10^17,LEFT(AN34,COLUMN(2:2))*1),
LOOKUP(10^17,LEFT(AP34,COLUMN(2:2))*1)
)

算出したい列同士の間隔は一定でないですね？。
『（』『）』は全角、半角統一されていますか？。
数値は１５０や２０００とケタが固定でなく不明ですね？。
年数も。

シートを加工して対応。
もともとの状態は保持したとして、
『（』を『,（』に置き換えてその加工シートをＣＳＶ形式で保存。
開き直せば、『（』の前と後で列が分かれる。
『（』の前＝求めている数値、となれば・・・・。

どれくらいまでできるかは試していませんが、参考です。

補足

layy様、お手数かけます。
エクセルからそのまま数式を取り出してコピー＆ペーストしてしまいました。
ご迷惑おかけしました。
以下インラインで回答します。
＞算出したい列同士の間隔は一定でないですね？
的を射た回答でないかもしれませんが、例に出したデータがセル内に入力されています。そういう意味では等間隔です。
＞『（』『）』は全角、半角統一されていますか？
はい。されています。全て半角です。
＞数値は１５０や２０００とケタが固定でなく不明ですね？。年数も。
はい、数字の桁数は統一されておりません。年数は現在、一桁で統一されています。なので計算に不必要な文字列数は固定されています。

後半部分ですが、シートを加工してその数字を持ってくるやり方はこの資料の引継ぎ性を考えると不可能です。ひとつのシートで完結出来ればと考えております。

以上、よろしくお願いします。

回答No.1

bin-chan

> データの例としては「150(4年間)」、「2000（6年間）」などです。
この場合、具体的にはどう扱うのですか？

「150(4年間)」は[150]だけを取り出せば良いのですか？
それとも、[150]と[4]を取り出せば良いのですか？
はたまた、[150]と[4年間]を取り出せば良いのですか？

「2000(6時間)」は[2000]だけを取り出せば良いのですか？
それとも、[2000]と[6]を取り出せば良いのですか？
はたまた、[2000]と[6時間]を取り出せば良いのですか？

補足

bin-chan様、お手数かけます。今回取り出したい数字「150(4年間)」を例にとれば150のところです。その後の4年間の4のところは関係ありません。その他の項目も同様です。以上よろしくお願いします。