☰

시간 문자열을 초로 변환하는 방법

tag-icon • tag-icon microsoft-excel
시간 문자열을 초로 변환하는 방법

Excel로 가져온 약 5000줄의 CSV 파일이 있는데 문제는 다음과 같은 여러 시간 문자열이 있어서 1h1m1s이를 초로 변환해야 한다는 것입니다.

다음은 몇 가지 예입니다.

A열의 데이터

0m11s         
2m32s        
3m10s        
1h2m35s

Col B의 결과

11 seconds
152 seconds  
190 seconds    
3755 seconds

셀 서식을 지정하려고 했지만 이상한 결과가 나왔습니다!

그렇게 할 수 있습니까? 그렇다면 시간 문자열을 초로 어떻게 변환할 수 있습니까?

답변1

VBA를 사용하면 비교적 쉽습니다.

한 가지 방법은 정규식을 사용하여 문자열을 구문 분석한 다음 각 섹션에 적절한 변환을 곱하는 것입니다.

이 사용자 정의 함수(UDF)를 입력하려면 alt-F11Visual Basic Editor를 엽니다. 프로젝트 탐색기 창에서 프로젝트가 강조 표시되어 있는지 확인하세요. 그런 다음 상단 메뉴에서 Insert/Module아래 코드를 선택하여 열리는 창에 붙여넣습니다.

이 사용자 정의 함수(UDF)를 사용하려면 다음과 같은 수식을 입력하세요.

=convSeconds(A1)

어떤 세포에서.

아래와 같이 초 수만 반환됩니다. 단어를 추가하려면 Seconds수식을 문자열과 연결하면 됩니다. 또는 사용자 정의 숫자 형식을 사용하십시오(결과의 숫자 품질이 유지됨).

Option Explicit
Function convSeconds(s As String) As Long
    Dim RE As Object, MC As Object
    Dim SEC As Long
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .ignorecase = True
    .Pattern = "(?:(\d+)h)?(?:(\d+)m)?(?:(\d+)s)?"
    If .test(s) = True Then
        Set MC = .Execute(s)
        With MC(0)
            SEC = SEC + .submatches(0) * 3600 'hours
            SEC = SEC + .submatches(1) * 60   'minutes
            SEC = SEC + .submatches(2)        'seconds
        End With
    End If
End With
convSeconds = SEC
End Function

여기에 이미지 설명을 입력하세요

정규식에 대한 설명은 다음과 같습니다.

h/m/s 추출

(?:(\d+)h)?(?:(\d+)m)?(?:(\d+)s)?

다음으로 생성됨정규식 친구

답변2

이를 수행하는 간단한 방법은 다음과 같습니다. 하나의 수식으로 모두 수행할 수 있지만 반복을 피하기 위해 몇 가지 도우미 열을 사용했습니다.

여기에 이미지 설명을 입력하세요

귀하의 예에는 항상 분과 초에 대한 자리 표시자가 하나 이상 있지만 시간은 시간이 있는 경우에만 포함됩니다. 첫 번째 단계에서는 "h"를 찾습니다. B1의 공식:

=FIND("h",A1)

"h"가 없으면 오류를 반환합니다. 그렇지 않으면 문자열에서 h의 위치를 ​​반환합니다(시간 값이 9를 초과할 수 있다고 가정하고 그렇지 않으면 왼쪽 문자를 사용할 수 있습니다).

C열은 항상 동일할 부분을 벗겨냅니다. C1의 공식:

=IF(ISERROR(B1),A1,MID(A1,B1+1,LEN(A1)))

"h"가 발견되지 않으면 원래 문자열을 사용하고, 그렇지 않으면 h 이후의 모든 문자열을 사용합니다.

D 열은 "m"을 찾습니다. D1의 공식은 B1의 공식과 유사합니다.

=FIND("m",C1)

E열에서는 조각을 사용하여 초를 계산합니다. E1의 공식:

=IF(ISERROR(B1),0,3600*LEFT(A1,B1-1))+LEFT(C1,D1-1)*60+MID(C1,D1+1,LEN(C1)-D1-1)

"h"가 발견되지 않으면 시간 기여도는 0이고, 그렇지 않으면 "h" 왼쪽에서 발견된 값의 3600배입니다. 분 기여도는 "m" 왼쪽에 표시된 값의 60배입니다. 초 기여는 "m" 뒤와 마지막 문자("s") 앞에 있는 숫자입니다.

B:D 열을 숨길 수 있습니다. 단일 수식에 모든 내용을 포함하려면 도우미 셀에 대한 참조를 관련 도우미 셀 수식으로 바꾸면 됩니다.

주의 사항: 이 공식은 항상 분과 초 값을 포함하는 질문의 데이터 특성을 다룹니다. 분이나 초가 반드시 포함되지 않는 상황에서는 더 복잡한 공식이 필요합니다.

답변3

입력 데이터가 A열 1행에 있다고 가정합니다.

다음 공식을 시도해 볼 수 있습니다(B1에 복사하여 붙여넣고 필요에 따라 아래로 드래그).

=(IF(ISERROR(FIND("h",A1)),0,INT(MID(A1,1,FIND("h",A1)-1)))*3600)+(INT(IF(ISERROR(FIND("h",A1)),LEFT(A1,FIND("m",A1)-1),MID(A1,IF(ISERROR(FIND("h",A1)),0,FIND("h",A1)+1),FIND("m",A1)-FIND("h",A1)-1)))*60)+INT(MID(A1,FIND("m",A1)+1,FIND("s",A1)-FIND("m",A1)-1))

답변4

이 질문에는 분과 초가 항상 포함된다는 단순화가 포함된 질문의 문제를 해결하는 답변이 이미 있습니다. 사람들은 유사하지만 필요한 시간 구성 요소만 포함되어 하나 이상의 구성 요소가 누락될 수 있다는 보다 일반적인 문제를 안고 있을 수 있습니다.

Ron Rosenfeld의 훌륭한 답변은 이미 이를 처리하고 있습니다. VBA 또는 정규식에 익숙하지 않고 더 쉽게 적응하고 유지 관리할 수 있는 수식 기반 솔루션을 선호하는 독자의 경우 다른 현재 답변은 일반적인 경우를 처리하지 않습니다. 이 수식 기반 솔루션은 이 질문의 제한된 사례에 필요한 것보다 더 복잡하고 다른 답변과 결합하면 두 솔루션의 접근성이 떨어지기 때문에 별도의 답변으로 게시하겠습니다.

여기에 이미지 설명을 입력하세요

이 솔루션은 또한 수식 반복을 피하기 위해 일부 도우미 열을 사용하지만 약간 다른 접근 방식을 취합니다. B열부터 D열까지 각각 다른 시간 구성 요소 문자를 찾습니다. 첫 번째 행의 수식은 다음과 같습니다.

B1:  =FIND("h",A1)
C1:  =FIND("m",A1)
D1:  =FIND("s",A1)

문자가 있으면 원래 문자열에서 해당 문자의 위치를 ​​반환하고, 그렇지 않으면 오류가 발생합니다. "s"가 있는 경우 항상 마지막 문자가 되지만 문자 위치를 찾는 것은 초 값 추출을 단순화하고 FIND는 존재 여부를 식별하는 두 가지 목적을 제공합니다.

E열은 각 시간 구성 요소 값을 추출하고 여기에 초 단위의 변환 계수를 곱한 후 합산합니다. MID를 사용하여 B열부터 D열까지의 마커 위치를 기반으로 값을 추출합니다.

복잡한 점은 몇 분과 몇 초 동안 이전 시간 구성 요소 중 일부 또는 전부가 존재하지 않을 수 있다는 것입니다. 수식은 이전 구성 요소 중 어떤 구성 요소가 있는지에 따라 문자열에서 현재 구성 요소가 시작되는 위치를 결정해야 합니다. E1의 공식은 다음과 같습니다.

=3600*IFERROR(LEFT(A1,B1-1),0)+
 60*IFERROR(MID(A1,1+IFERROR(B1,0),C1-1-IFERROR(B1,0)),0)+
 IFERROR(MID(A1,1+MAX(IFERROR(B1,0),IFERROR(C1,0)),D1-1-MAX(IFERROR(B1,0),IFERROR(C1,0))),0)

가독성을 위해 시간 구성 요소 사이에 수식에 줄 바꿈을 추가했으므로 이를 제거하여 수식을 복사하여 붙여넣습니다.

시간은 매우 간단합니다. "h" 앞의 왼쪽 문자를 취하고, "h"가 누락된 경우 0을 가져와서 3600을 곱합니다.

분 값 앞에는 시간만 올 수 있습니다. 분 값이 있으면 시간 구성 요소가 있는지 여부에 따라 MID의 시작점과 길이를 계산합니다.

초 값의 위치(있는 경우)는 시간 및 분 구성 요소 중 하나 또는 둘 모두의 존재 여부에 따라 달라집니다. "m"(있는 경우)의 위치는 항상 문자열에서 "h"(있는 경우)의 위치보다 뒤에 있습니다. 둘 중 하나라도 없으면 수식은 해당 구성 요소에 대해 0 값을 사용합니다. 그런 다음 MAX 함수는 원래 문자열에서 초 값 앞에 있는 가장 먼 위치를 제공하고 문자열 길이 중 이전 시간 구성요소가 차지하는 정도를 결정합니다.

도우미 열을 숨길 수 있습니다. E열의 수식에 통합할 수 있지만 해당 값이 사용된 횟수를 살펴보면 결과 수식은 엄청나게 길고 유지 관리가 어려울 것입니다.

관련 정보