[미친 활용 24] 휴대폰 번호의 사라진 앞자리 0 표시하기
엑셀에서는 ‘01012341234’, ‘01234’와 같이 숫자 앞에 0을 입력하면 이 값을 계산이 가능한 숫자로 취급하고, 맨 앞의 0은 계산에 영향을 끼치지 않는 불필요한 자릿수로 판단해 0을 지웁니다. 0을 표시하기 위해 이전까지는 연락처 앞에 작은따옴표(‘)를 넣어주거나 010-1234-1234와 같이 하이픈을 입력했을 겁니다. 여기에 셀 표시 형식을 활용하는 방법도 있으니 다음 실습을 따라 해보세요.
01 ➊ 연락처가 적힌 셀을 모두 선택하고 단축키 Ctrl+1을 누릅니다. ➋ [셀 서식] 대화상자의 [표시 형식] 탭에서 ➌ [사용자 지정]을 선택합니다.
02 ➊ [형식]에서 G/표준을 삭제하고 000-0000-0000을 입력한 후 ➋ [확인]을 클릭합니다. 연락처의 맨 앞에 0이 표시되고 연락처 형식에 맞게 하이픈이 표시됩니다.
셀 표시 형식에서 0은 숫자 자릿수를 지정하는 데 사용하는 코드입니다. 실젯값이 0이거나 값이 없는 때에 그 자릿수를 0으로 표시하게 만드는 역할을 합니다. 전화번호와 같이 0이 맨 앞자리로 와야 할 데이터를 입력할 때는 셀 표시 형식을 활용해보세요.
[AI 치트키] 휴대폰 번호 형식 통일하기
엑셀에 휴대폰 번호를 입력했을 때 맨 앞의 0이 사라져서 ‘1012345678’처럼 입력된다면 다음과 같이 요청해보세요. 위 방법처럼 셀 표시형식을 변경하는 방식도 좋지만, 원본 데이터를 AI에게 ‘010-1234-5678’ 형식으로 통일시키는 것을 요청하는 것도 좋습니다.
[나] : 엑셀에 휴대폰 번호를 입력하니까 맨 앞의 ‘0’이 자꾸 지워져서 ‘1012345678’ 형태로 보여. C5:C20 범위에 있는 데이터들의 맨 앞에 0을 한 번에 붙여서 정상적인 번호로 바꾸고 싶어.
기존 숫자를 0이 포함된 텍스트로 바꾸는 TEXT 함수식이나, 입력 형식을 유지하는 사용자 지정 셀 서식 설정 방법 또는 ‘010-1234-5678’의 형태로 원본 데이터를 모두 정리해서 전달해줘.
[미친 활용 25] 줄바꿈된 데이터를 한 줄로 한 번에 정리하기
줄바꿈된 데이터를 한 줄로 만들어야 할 때 SUBSTITUTE 함수를 사용해보세요. 보이지 않는 줄바꿈 기호를 찾아 원하는 구분자로 바꿔주면, 데이터를 한 줄로 손쉽게 정리할 수 있습니다. 이번 실습에서는 줄바꿈 기호인 CHAR(10)을 쉼표(,)로 바꾸어 데이터를 한 줄로 만들겠습니다.
01 ➊ 수식을 입력할 셀을 선택하고 F2를 누릅니다. ➋ 커서가 깜박이면 SUBSTITUTE(B3,CHAR(10),",")을 입력하고 Enter를 누릅니다.
02 줄바꿈되어 있던 데이터가 한 줄로 표시됩니다.
< 함수 치트키>
[B3] 셀의 값에서, 줄바꿈을 나타내는 CHAR(10)를 찾습니다.
문자열 내에서 찾은 줄바꿈 기호를 바꿀 문자인 쉼표(,)로 치환합니다.
바꿀 지점을 지정하지 않았으므로, 지정한 문자열 내의 모든 줄바꿈 기호를 쉼표(,)로 치환하여 한 줄로 표현하게 됩니다.
[AI 치트키] 줄바꿈 된 데이터를 한 줄로 연결하기
줄바꿈 기호처럼 눈에 보이지 않는 문자를 함수로 처리해야 할 때는 AI에게 원하는 결과와 원본 데이터가 입력된 셀, 바꿔 넣을 구분 기호를 정확히 알려주세요. 사용하는 엑셀 버전까지 함께 명시하면 해당 버전에서 바로 입력할 수 있는 수식을 AI가 알려줄 겁니다.
[나] : ‘엑셀 2021’ 버전에서 한 셀 내에 줄바꿈 되어 있는 데이터를 줄바꿈 대신 쉼표를 이용해 구분하고 한 줄로 나타내고 싶어. 줄바꿈 되어 있는 데이터는 B3셀에 입력되어 있고, C3셀에 입력할 수식을 만들어줘.
[미친 활용 26] 줄바꿈된 데이터를 열마다 나눠서 정리하기
바로 앞 실습에서 줄바꿈된 데이터를 한 줄로 표현하는 것 외에 여러 줄로 적혀 있는 데이터를 각 셀로 분리하려면 어떻게 해야 할까요? 언뜻 엑셀 기능 중에서 ‘텍스트 나누기’가 떠오르기도 할 겁니다. 그런데 텍스트 나누기 기능에는 줄바꿈에 대한 항목이 명시되어 있지는 않습니다.
이때는 텍스트 나누기 기능에서 줄바꿈 기호를 인식할 수 있도록 설정해주면, 손쉽게 텍스트를 분리할 수 있습니다.
01 ➊ 줄바꿈되어 있는 데이터를 모두 선택하고 ➋ [데이터 → 데이터 도구 → 텍스트 나누기]를 클릭합니다.
02 ➊ [구분 기호로 분리됨]이 선택된 것을 확인하고 ➋ [다음]을 클릭합니다. ❸ [구분 기호]에서 [기타] 선택 ➍ 입력란을 클릭한 후 Ctrl+J를 누르고 ➎ [마침]을 클릭합니다.
03 하나의 셀 안에서 줄바꿈되었던 데이터가 각 셀로 분리됩니다.
단축키 Ctrl+J는 일반적인 상황에서 줄바꿈으로 인식되는 단축키는 아닙니다. 셀 내부에서 데이터 줄바꿈을 하고 싶다면, 단축키 Alt+Enter를 누르면 됩니다.
‘해당 영역에 이미 데이터가 있습니다. 기존 데이터를 바꾸시겠습니까?’라는 메시지 창이 나타나면 [확인]을 클릭합니다.
[AI 치트키] 빠르게 데이터 분류하기
마감기한이 촉박하여 직접 엑셀 작업이 힘든 상황이라면, 원본 데이터를 그대로 주고 AI에게 지시해보세요. 지시할 때는 분류 기준을 반드시 명시하고, 해당 데이터를 바로 엑셀에 붙여넣을 수 있도록 전달해달라고 지시하세요.
[나] : 해당 데이터는 ‘이름, 나이, 성별’ 순서대로 줄바꿈 표기되어 있어. 분류가 완료된 데이터를 바로 복사해서 붙여넣기를 할 수 있도록 항목별 데이터를 모두 분류해서 정리해줘.
[미친 활용 27] 구분 기호가 없는 데이터에서 원하는 값만 손쉽게 추출하기
데이터에 구분 기호가 있으면 텍스트 나누기 기능을 통해 데이터를 손쉽게 나눌 수 있었습니다. 그렇다면 다음과 같이 제품명만 분리하고 싶은데, 제품코드와 제품명을 구분할 구분 기호가 없을 때는 어떻게 데이터를 분리할 수 있을까요?
이때는 LEN 함수와 RIGHT 함수를 조합해 원하는 데이터를 손쉽게 추출하겠습니다.
01 ➊ [제품명] 열에서 임의의 셀을 선택하고 F2를 누릅니다. ➋ 커서가 깜박이면 =RIGHT(B4,LEN(B4)-8)을 입력하고 Enter를 누릅니다.
< 함수 치트키>
LEN(B4) : [B4] 셀에 적혀 있는 문자열(공백 포함) 개수를 구합니다.
LEN(B4)-8 : [B4] 셀의 문자열 전체 개수에서 제품 코드를 나타내는 문자 ‘[G-001] ’ 개수(공백 포함하여 총 8글자)인 8을 뺍니다.
RIGHT(B4,LEN(B4)-8) : LEN(B4)-8의 결과만큼 [B4] 셀의 문자열을 오른쪽에서부터 반환합니다.
다음 표를 보면 [B4] 셀의 값을 기준으로 수식이 어떻게 동작하는지 쉽게 이해할 수 있습니다.
[AI 치트키] 구분 기호가 없는 데이터 분류하기
우리가 눈으로 보았을 때는 데이터를 구분할 수 있지만, 엑셀 수식으로 적용하기에는 특정 규칙이 일정하지 않은 데이터라면 AI에게 데이터 분류 기준과 분류된 예시 데이터를 같이 알려주면서 작업을 요청해보세요.
[나] : 해당 데이터는 ‘상품코드’와 ‘상품명’이 같이 적혀 있는 데이터가야. 특정한 규칙이 없는 데이터이므로, 제시하는 샘플 데이터를 분석하여 나머지 데이터도 이에 맞게 분류하고, 엑셀에 바로 붙여넣기를 할 수 있도록 결과를 전달해줘. (상품코드 : G-001, 상품명 : 산업용 드릴)