[[20220306231202]]　『時間の数字だけ取り出したい』（yuri）　ページの最後に飛ぶ

『時間の数字だけ取り出したい』（yuri）

いつもお世話になっております。

本来、1'23"45としたいのですが、過去のデータが1:23.45や1'23.45となっています。
HOUR関数やMINUTES関数などでやってみましたが、取り出すことが出来ません。

そのままの数字だけを取り出すにはどうすればよいのかご教示願います。

よろしくお願いいたします。

＜使用 Excel：Excel2010、使用 OS：Windows10 ＞

たぶん文字列になっているからでしょう。

"'" と "." を ":" に置換したうえで、
TIMEVALUE関数の引数にすることで、
時刻を表すシリアル値に変換できるはずです。
そうすれば、HOUR関数等が使えると思います。

どのくらいの数があるか不明ですが、手作業でもさほど負荷はかからないのでは？
マクロで書き換えることもできると思いますが。

（γ） 2022/03/07(月) 07:42

Y様、こちらの件もありがとうございます。

置換というものを初めて知りました。
マクロも全く分からない初心者ですが、勉強になりました。

重ね重ねありがとうございました。
（yuri） 2022/03/07(月) 08:30

 ワークシート関数であればSUBSTITUTEでセル単位の置換ができます。
 B1セルに
 =TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A1,".",":"),"'",":"))
 とすると、A1の文字列を元に、シリアル値を設定します。
 こんな感じです。

 以下、【参考レベル】

 もしマクロを使うなら、こんな風なことでしょうか。
 おまじない的な感じのコードです。

 対象範囲を選択状態にしたうえで以下のマクロを実行してください。

 セルごとに、
 1:23.45 や 1'23.45　のような文字列となっているセルだけを対象として、
 シリアル値に変換し、書式を変更します。

 Sub test()
     Dim re As Object
     Dim r  As Range
     Dim v
     Dim s  As String

     Set re = CreateObject("VBScript.RegExp")
     re.Pattern = "(\d{1,2}[:'])?(\d{1,2}(?:''|:|\.))(\d{1,2})"

     For Each r In Selection
         If r.Value <> "" Then
             If TypeName(r.Value) = "String" Then
                 s = r.Text
                 If re.test(s) Then
                     v = Replace(Replace(Replace(s, "''", ":"), ".", ":"), "'", ":")
                     v = TimeValue(v)                    'シリアル値に変換
                     r.NumberFormatLocal = "h'mm''ss"    '   1'05''03 形式の表示
                     r.Value = v
                 End If
             End If
         End If
     Next
 End Sub

（γ） 2022/03/07(月) 08:59

 1'23"45は1分23秒45だと思うのだが。
 (なので過去データで1:23.45がある)
 だとすると
 1'12"99
 とかもあり得ると思うが。
 なおこの表記の場合1時間20分50秒20は
 1°20′50″20
 になる。
（ねむねむ） 2022/03/07(月) 09:20

あ、なるほど。じゃあ、私のは全滅ですね。つられてしまった。
没にしてください。失礼しました。

（γ） 2022/03/07(月) 09:47

 これは表示が
 1'23"45
 になればいいのだろうか？
 それとも表示だけでなくデータ形式も時刻データにしたいのだろうか？
（ねむねむ） 2022/03/07(月) 10:34

 もう一つ。
 データは分・秒だけなのだろうか?
 時が出てくることはあるのだろうか？
（ねむねむ） 2022/03/07(月) 10:38

その後、進展がありませんが、当初の発言
| 本来、1'23"45としたいのですが、過去のデータが1:23.45や1'23.45となっています。
| HOUR関数やMINUTES関数などでやってみましたが、取り出すことが出来ません。
からすると、HOUR関数と言っているので、
1'23"45を
1時間23分45秒の積りで書かれているのかもしれませんね。

もしそうなら、":"に置換してTimevalueを使う方針はよいのかもしれません。
表示形式を"h°mm'ss''" に修正すればよいのかもしれません。

# いずれにせよ、質問者さんの答え待ちでしょうか。
（γ） 2022/03/08(火) 07:09

Y様、ねむねむ様、ご返信ありがとうございます。
全く気付いておらず、大変失礼いたしました。

1分23秒45で合っております。
本当のタイムは一番遅くて3'00"00である為、1時間にいくことは絶対にありません。
なので【1'23"45】の表示で統一したいと思っておりました。

HOUR関数を使ってみたのは過去のデータが1時間表示など人によって入力の仕方がバラバラであった為、初心者ながらですが、変換出来る所はやってみようと試みた次第です。
（yuri） 2022/03/08(火) 21:15

 >これは表示が
 >1'23"45
 >になればいいのだろうか？
 >それとも表示だけでなくデータ形式も時刻データにしたいのだろうか？
 への回答は？
（ねむねむ） 2022/03/09(水) 09:24

コメント返信：

[ 一覧(最新更新順) ]

[[20220306231202]] 『時間の数字だけ取り出したい』（yuri） ページの最後に飛ぶ

[[20220306231202]]　『時間の数字だけ取り出したい』（yuri）　ページの最後に飛ぶ