[엑셀팁] 마구잡이로 기록된 엑셀 날짜 자동 변환

천라의 2023. 12. 7. 15:36

엑셀로 기록된 데이터를 살펴보다보면 날짜 정보가 마구 잡이로 기록된 데이터들이 있습니다.
예를 들어
2023년4월01일
이렇게 년월일이 붙어 있다던지
04월 1일 2023년
이렇게 월 일 년 순이라던지
혹은
20230401
이런식으로 숫자로만 되어 있다던지
이렇게 다양한 유형으로 기입된 엑셀 날짜 데이터를 자동으로 변환하는 방법을 공유합니다.
다만 데이터 유형에 따라 방법이 다를 수 있으니 사정에따라 응용해서 적용해보세요
약간 복잡할 수 있으나 최대한 간결하고 자세히 설명하겠습니다
대부분 텍스트 마법사 텍스트 나누기 기능으로 일괄 날짜 자동 변환이 가능하니 참고하세요!

1.연월일이 붙어있는 텍스트 엑셀 날짜 자동변환

2023년04월1일, 2023년4월1일 이런식으로 붙어 있는 경우는 텍스트 마법사 텍스트 나누기 기능으로 텍스트를 쪼개준 후에 textjoin함수로 간단하게 날짜 데이터로 바꿔줄 수 있습니다.
먼저 해당 데이터를 드래그로 영역지정 선택해주세요

선택된 상태에서 데이터 - 데이터 도구 - 텍스트 나누기 선택

텍스트 나누기 창에서 너비가 일정함 선택 - 다음 클릭

하단 데이터 미리 보기 창에서 년 / 월 / 일 사이에 클릭하여 구분선 추가 후 다음

마침 클릭

그러면 다음과 같이 텍스트가 나뉘면서 각 열에 저장 됩니다.

이렇게 변환된 데이터를 TEXTJOIN() 함수로 묶어주겠습니다.

=TEXTJOIN() 함수 설명

=TEXTJOIN() 함수는 각 셀의 텍스트를 하나의 셀로 합쳐주는 역할을 합니다.
함수에 필요한 인자는 다음과 같습니다.
=TEXTJOIN( 각 텍스트 사이에 들어갈 텍스트(”텍스트”), 빈셀 무시여부 (True=기본값 무시, False 무시하지 않음), Text1(셀 위치 혹은 “텍스트”), Text2……)

첫 행의 마지막 열 첫 셀에 =TEXTJOIN( 을 먼저 입력해주세요.

그리고, 각 셀의 내용 사이사이에 띄어쓰기가 들어가야하니,< “ “ >를 첫번째 인자로 적어줍니다.
=textjoin(” “,

여기는 빈셀이 없으니 그냥 다음 인자는 비워둡니다.
=textjoin(” “,,

그다음으로 각 셀을 선택하여 인자값을 넣어주세요
=textjoin(" ",,A1,B1,C1

그리고 엔터를 누르면 다음과 같이 띄어쓰기가 적용된 엑셀 날짜 자동 변환이 됩니다.

이걸 모든 행에 적용하기 위해 드래그 복사해주세요

이 함수로 나오게 된 결과 값은 텍스트 정보여서 VALUE() 함수로 숫자 데이터로 변경이 필요합니다. (텍스트가 셀 왼쪽으로 붙어있죠?)
VALUE() 함수로 일괄 변환을 할게요.
복사할 열 오른쪽 열 첫 셀을 선택하고
=value( 를 입력해주세요.

그다음 첫 행의 셀을 지정해주세요.
=value(E1

그다음 엔터를 입력합니다.
그러면 이상한 숫자가 나오는데, 이 숫자가 엑셀이 인식하는 날짜 숫자 입니다.
이걸 서식 설정으로 바꿔줄게요.

해당 셀을 선택하고
홈 - 표시형식 - 서식 드롭다운 - 간단한 날짜 선택해주세요.

그러면 아래와 같이 날짜 서식으로 변경되게 되는데,

이걸 드래그 다운 복사해주세요

이것도 역시 함수 값이라서 값으로 복사 붙여넣기 과정을 한번 더 합니다.

그러면 해당 날짜가 엑셀 날짜 자동 변환이 완료 됩니다.

해당 열을 복사하여 원본 데이터에 붙여넣어주시면 됩니다.

2.월 일 년 순으로 되어 있는 텍스트 엑셀 날짜 자동 전환

예를 들어 04월 1일 2023년 이런식으로 띄어쓰기는 되어 있는데, 월 일 년 순으로 되어있는 텍스트 날짜 데이터를 엑셀 날짜 자동 변환을 해보겠습니다.

이 경우는 위와 같이 텍스트나누기 및 TEXTJOIN -> VALUE 함수 적용은 같으나, 텍스트 나누기 이후 열만 옮겨주면 됩니다.

3. 숫자로만 이뤄진 엑셀 날짜 데이터 자동 변환

202304301 이런 식으로 숫자만 되어있는 엑셀 날짜 데이터도 같은 방식으로 텍스트 나누기 연/월/일 기준으로 나줘주고

TEXTJOIN 함수로 연월일 사이마다 "-" (하이픈)을 넣어주면 됩니다.
=TEXTJOIN("-",,A1,B1,C1

이후엔 VALUE(셀 위치) - 복사 - 값으로 붙여넣기 하고 서식만 날짜로 바꿔주세요
이렇게 텍스트로 구성되어 있는 엑셀 날짜 데이터 자동 변환하는 방법을 알아보았습니다.
*만약 붙어있는 상태에서 일월년(1일4월2023년) 혹은 월일년 (4월1일2023년)이렇게 되어 있다면 텍스트를 나누고 해당 열을 옮겨붙여서 textjoin()함수로 합쳐주세요.
*다른 함수로 간결하는 방법도 있겠으나 복잡해도 직관적고 다양한 날짜 데이터 형태에도 응용이 가능한 방식으로 설명했습니다.