[[20210121184712]] 『一つのセルに時間相当の文字列が混入している場合』(動画ファン) >>BOT

[ 初めての方へ | 一覧(最新更新順) |

| 全文検索 | 過去ログ ]

 

『一つのセルに時間相当の文字列が混入している場合の抜き出し』(動画ファン)

一つのセルに時間相当の文字列が混入している場合
時間相当及びそれ以外を抜き出して別セルに記載する方法を教えて下さい。

時間相当は、
 m:ss又はmm:ssであり1時間以上になることはありません。
 時間を抜き出したセルの表示形式は、hh:mm:ssです。

例えば、
 B列に以下が記入している場合
  Koiwa itumo uwakismono13:21
  sonoba kagiri no monogatari5:50

 C列に
  Koiwa itumo uwakismono
  sonoba kagiri no monogatari

 D列に
  00:13:21
  00:05:80


又、以下のように
  Koiwa itumo uwakismono 13:21
  sonoba kagiri no monogatari 5:50

時間相当の文字列の前に半角の空欄がある場合はどうなりますか ?

< 使用 Excel:Excel2019、使用 OS:Windows10 >


こちらで教えてもらった。
式を少し変えて以下のようにしましたが

=TEXT(LOOKUP(10^17,RIGHT(B1,COLUMN($1:$1))*1),"mm:ss")
=TRIM(SUBSTITUTE(B1,C1,""))

時間相当部分
 
  13:21 --->21:00
  5:50 ---->50:00 

それ以外

  Koiwa itumo uwakismono13:21 ----> Koiwa itumo uwakismono13:21

と上手く抜き出しできませんでした。

(動画ファン) 2021/01/22(金) 08:32


=TIMEVALUE("00:"&TEXT(LOOKUP(10^17,RIGHT(A1,COLUMN($1:$1))*1),"hh:mm"))
これでどうでしょうか?
(シオラ) 2021/01/22(金) 12:04

 >=TEXT(LOOKUP(10^17,RIGHT(B1,COLUMN($1:$1))*1),"mm:ss")
                               ~~~~↑~~~~~~~~
            こう言うのはもう止めましょうよ。(XL2007以降は1行が16千列もあり、無駄過ぎます)

 (1) C1セル =TRIM(LEFT(B1,LEN(B1)-4-COUNT(LEFT(RIGHT(B1,5))*1)))
 (2) D1セル =RIGHT(B1,4+COUNT(LEFT(RIGHT(B1,5))*1))/60

 <結果図>
  行  ________________B________________  _____________C_____________  ____D____
   1  Koiwa itumo uwakismono13:21        Koiwa itumo uwakismono       00:13:21 
   2  sonoba kagiri no monogatari5:50    sonoba kagiri no monogatari  00:05:50 
   3  Koiwa itumo uwakismono 13:21       Koiwa itumo uwakismono       00:13:21 
   4  sonoba kagiri no monogatari 5:50   sonoba kagiri no monogatari  00:05:50 

(半平太) 2021/01/22(金) 12:09


 時間は必ず文字列の最後?

 >00:05:80 
 00:05:50 の間違い?
       ~~
 だとして、時間の抜き出しだけ

 =-LOOKUP(1,-RIGHT(B1,{1,2,3,4,5}))/60
 表示形式〜ユーザー定義 hh:mm:ss

 参考まで
(笑) 2021/01/22(金) 12:37

 もうひとつ確認・・・

 >m:ss又はmm:ssであり
 これは 05:50 のように、分が「0」で始まる場合もあるってこと?

 それとも分が1桁と2桁の場合があるということを言いたかっただけで
 分が1桁なら 5:50 であり、05:50 のようなものはないってこと?

 後者なら、時間以外の抜き出しは

 C1 =TRIM(SUBSTITUTE(B1,TEXT(D1,"m:ss"),""))

 参考まで
(笑) 2021/01/22(金) 13:19

皆さん、回答をいただき感謝いたします。

笑さん、
 >00:05:50 の間違い?
     YES、記載ミスです。
 >05:50 のように、分が「0」で始まる場合もあるってこと?
     YES、0で始まることもあり5から始まる場合もありえます。

それぞれ回答者さんの式で、うまく処理できましたが
 半平太さんの式は、考えられるケースの全て適応して抜き出しが上手く行って嬉しいです。

行 ________________B________________ _____________C_____________ ____D____
1 Koiwa itumo uwakismono13:21 Koiwa itumo uwakismono 0:13:21
2 sonoba kagiri no monogatari5:50 sonoba kagiri no monogatari 0:05:50
3 Koiwa itumo uwakismono 13:21 Koiwa itumo uwakismono 0:13:21
4 sonoba kagiri no monogatari 5:50 sonoba kagiri no monogatari 0:05:50
5 sonoba kagiri no monogatari05:50 sonoba kagiri no monogatari 0:05:50
6 sonoba kagiri no monogatari 05:50 sonoba kagiri no monogatari 0:05:50

(動画ファン) 2021/01/22(金) 16:33


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.