Excelで時間風なテキストデータを10進表示時間数に変換したい

質問

いまA列に、WebやOCRで取り込んだ、時刻データでない一見時間風なテキスト文字列があります。
　これをB列に表示のように10進数の時間数値に変換する１個の数式を考えていますが、私の知恵ではすごく長い式になります。
　これを最短の数式で現すベスト（又はベター）な方法をお尋ねします。
　私が考えたプロセスは、まずB列に設けた時間の9や10を取り出す式（LEFT,FIND等）を皮切りに、順次右の列に分の15や6を取り出す式（MID,FIND）、6に0を加えて「06」とする式、分に10/6を乗じ、四捨五入して小数の時間に変える式、整数と小数の各時間を加算する式などを設け、最終的にその右列に答えを出す。あるいは、これらの式を必要か所に代入して１個の式にまとめる、などやってみましたが、どうしても長蛇の列になってしまいます。
　別の方法ではSUBSTITUTE関数を使って「9時間15分」を「9.15」に置き換えて数値化し、その上で10進数に置き換えることも試みましたが、SUBSTITUTEのネスト、分の部分の6を06に変える式、10/6を乗じて四捨五入する式などもあり、これも結構長たらしくなります。
　また、データ→区切り位置で分割するのも、例示の場合のように縦に整列されていないと結構面倒です。
　このほか小数点一桁と二桁の混在表示という表示形式の問題もあります。
　Excelでテキスト、日時、数値各データを相互に変換するのに結構面倒な場合が多いので、サンプルを例題として、表示形式の設定と併せ、関数のみの最短の数式で現す方法について、Excelご精通の方のお知恵を拝借したいと存じます。よろしくお願いします。

A列（テキスト）　B列（10進数値の時間）
5時間45分 　　　9.75 ←（5.75時間）
10時間6分 　　　10.1
7時間07分 　　　7.12

回答No.3

Wendy02 · ベストアンサー

こんにちは。Wendy02です。

＞どんなテキストデータでも、日付や時刻の表示形式に変えると同時にデータの性質がテキストから日時（シリアル値）に変更されるということでしょうか。

これも、「自動キャスト」と呼ばれる機能のひとつだと思います。

全て試したわけではありませんが、標準形式の中の日付や時刻の表示に該当するものは、演算（×1　または、--)をさせると、それが、シリアル値に変わるはずです。

入力したときの文字列は、不安定な状態にあるので、あるきっかけで、文字列が他の型（日付型－日付型の小数点以下が、時間値）に変わってしまうわけですから、良い面もありますが、困った機能でもありますね。例えば、CSVでインポートした時などにも、こういう現象が適用されてしまうわけです。

お礼

早速のご教示ありがとうございます。
＞「自動キャスト」と呼ばれる機能
はじめて目にする文字です。
＞標準形式の中の日付や時刻の表示に該当するものは演算（×1　または、--)をさせると、それが、シリアル値に変わる……
これも知りませんでした。
なるほど、知っておくと使いようによってはすごく便利な機能ですね。
＞困った機能でもありますね
これは分かります。かって仕事で何でだろう、と困ったことを思い出しました。
ともかく今日は（いつものことですが）大変勉強になりました。またご指導を仰ぐかと思いますがよろしくお願いします。

回答No.2

Wendy02

こんにちは。
これでどうかしら？

=ROUND(SUBSTITUTE(A1,"間","")*24,2)

お礼

ご即答誠に感謝します。
さすがWendy02さん。驚きました。
こんな簡単なことだったんですか。
「間」を除去するだけでテキストデータが時間データに変わるなんて、全く思いつきませんでした。
やはりExcelの基本を勉強してないとダメですね。
相変わらず「思考の無限ループに陥って」しまってたようです。
でもやっぱりよく分かりません。どんなテキストデータでも、日付や時刻の表示形式に変えると同時にデータの性質がテキストから日時（シリアル値）に変更されるということでしょうか。
最初ウッソー！と思って慌てて「10山30谷」と入力し、入れ子のSUBSTITUTEで「：，空白」に置き換えて試してみたらちゃんと時刻になるんですね。
これまで長時間考えたのがバカみたいでした。(;＞_＜;) こういうことはマニュアルにも書いてないし、一体どういう仕組みになっているのか、差し支えなければ教えていただけませんか。
ともかくほんとにありがとうございました。m(_ _)m

回答No.1

shkwta

とりあえず思いつくのはこんなのですが。
=24*(SUBSTITUTE((SUBSTITUTE(A1,"時間",":")),"分",""))

小数第３位四捨五入が必要なら、
=ROUND(24*(SUBSTITUTE((SUBSTITUTE(A1,"時間",":")),"分","")),2)

お礼

ご即答感謝します。
SUBSTITUTEは最初考えたのですが、時刻の表示形式にした文字列に変えることで、同時にテキストデータが時刻データに変化するとは考えつきませんでした。
最初ご教示の
=SUBSTITUTE(SUBSTITUTE(A1,"時間",":"),"分",""))
まではやったんですが、それだけではまだテキストデータのままだと信じ込んでました。
その段階で数値変換を試してみるとよかったんですね。
ありがとうございました。