엑셀 날짜 서식 변경에 유용한 substitute 함수

엑셀 날짜 데이터가 이상한 경우

저 같은 경우는 주로 마케팅 데이터를 자주 다루기 때문에, 필연적으로 날짜와 각종 트래픽 지표를 매일 같이 누적하고 관리합니다. GA4부터 메타 광고, 네이버 광고, 구글 광고 등 각종 데이터를 한데로 엮어서 관리하고 보고하기 때문에 당연히 일자별로 데이터를 취합하고 정리해야 하지요. 그래서 자동화된 시트를 만들어놓고 Daily 업무를 진행하는 편입니다.

그런데 간혹 날짜별 데이터를 다운받으면 이상한 형식의 날짜 데이터를 보게 됩니다.

날짜로 인식되지 않는 형식

이전 게시물에서도 언급한 것 처럼,

2023.11.06 - [엑셀, 스프레드시트 (저장각)] - 엑셀 날짜 오류 ### 처럼 보일때 원인과 해결 방법

엑셀이나 스프레드시트는 기본적으로 날짜에 맞는 데이터 형식이 있습니다. 아래의 이미지 처럼 말이지요.

그런데 간혹 23.08.01. 과 같이 꼭 한번씩 수정을 해줘야 날짜로 인식할 수 있는 형식의 데이터들이 있습니다... 후...

다양한 케이스가 있지만 이번엔 2023. 8. 18. 과 같은 형식의 날짜를 함수를 통해 자동화하여 변경해보도록 하겠습니다.

 

이번 케이스는 substitute 와 len 함수를 활용합니다.

먼저 함수에 대해 간단히 설명해보겠습니다. SUBSTITUTE 함수는 Microsoft Excel에서 문자열에서 특정 문자나 문자열을 다른 문자나 문자열로 교체하려고 할 때 사용되는 함수입니다. 이 함수는 특정 텍스트에서 지정한 텍스트를 찾아 교체하거나 모든 발생을 교체할 수 있습니다.

 

SUBSTITUTE 함수는 다음과 같이 구성되어 있습니다.

=SUBSTITUTE(원본_텍스트, 찾을_텍스트, 대체할_텍스트, [발생_번호])

• 원본_텍스트: 교체하려는 원본 문자열 또는 셀 참조입니다.
• 찾을_텍스트: 원본_텍스트에서 교체하려는 문자열 또는 텍스트입니다.
• 대체할_텍스트: 찾을_텍스트를 교체할 문자열 또는 텍스트입니다.
• [발생_번호] (선택사항): 지정된 경우, 이것은 교체할 텍스트가 찾을_텍스트 중 어떤 발생을 교체할지를 지정합니다. 생략하면 모든 발생을 교체합니다.

예를 들어, 다음과 같은 SUBSTITUTE 함수를 사용하여 문자열에서 특정 단어를 교체할 수 있습니다:
=SUBSTITUTE("12345", "345", "12")

이 함수는 "12345" 문자열에서 "345"를 찾아 "12"로 교체합니다. 결과는 "1212"가 됩니다.

 

SUBSTITUTE 함수는 텍스트 처리 및 데이터 정제 작업에 유용하며, 특히 대량 데이터에서 특정 문자열을 교체하려는 경우에 자주 활용됩니다.

 

LEN 함수는 문자열의 길이를 반환하는 함수입니다. 이 함수를 사용하면 특정 셀 또는 문자열의 문자 수를 측정할 수 있습니다.

=LEN(텍스트)

 

예를 들어, =LEN("12345") 의 결과는 5가 됩니다. 12345 라는 문자열의 길이가 5개의 문자로 되어있기 때문에 그 길이만큼의 숫자를 반환해주는 것 입니다.

 

실전 함수! 날짜로 인식되는 형식으로 변경해보자

 

위의 이미지를 참고하시면, 2023. 7. 25.로 되어있는 A열의 날짜가 보입니다. 일반적으로 사용하는 날짜 형식인 2023-7-25 형식으로 변경하려면 어떻게 해야 할까요?

• 먼저 Left 함수와 Len 함수를 써서 맨 오른쪽의 .을 제외한 2023. 7. 25의 형태로 데이터를 변환시켰습니다.
• 그리고 다시 한번 substitute 함수를 입혀서 ". " 라는 문자를 "-" 라는 문자로 변환시켰습니다.
• 그 결과는 2023. 7. 25. => 2023-7-25 로 자동 변환되었습니다.
• 아래의 이미지 G열처럼 함수를 걸어놓으면, 이제 A열에 이상한 날짜 데이터를 넣기만해도 자동으로 G열에 정상적인 날짜 형식으로 변환되어 나옵니다.

엑셀, 스프레드시트에서 날짜는 정말 중요합니다.

대부분의 데이터를 다룰때 날짜가 너무 중요합니다. 특정 기간의 데이터를 정의하고 재편성하기 위해서는 반드시 정확한 날짜 데이터가 매칭되어야 하는 것이죠. 위의 예시 말고 또 다른 이상한 날짜 형식의 데이터 케이스가 발생한다면 다시 다른 해결책을 들고 글을 올려보겠습니다!