플래시 필(Flash Fill) 활용하기
한 열의 데이터를 다른 형식으로 변환하거나 여러 열로 나누어 추출하는 방법은 많은 엑셀 사용자가 자주 묻는 질문 중 하나입니다.
가장 먼저 플래시 필(Flash Fill) 기능을 활용하는 방법을 살펴보겠습니다. 플래시 필은 Microsoft Excel 2013 이상 버전에서 새롭게 추가된 기능입니다.
아래 예시에서는 A열에 직원들의 이름과 성이 나열되어 있습니다. 플래시 필이 작동하려면 반드시 원본 열이 필요하며, 여기서는 직원 이름이 담긴 A열이 그 역할을 합니다. 플래시 필은 이메일 주소처럼 일정한 패턴으로 반복되는 데이터를 만들 때 특히 유용합니다. 텍스트 수식 역시 이메일 주소를 생성할 때 폭넓게 활용되며, 간단한 것부터 복잡한 것까지 다양한 방식이 있습니다. 이 글에서는 플래시 필을 이용한 이메일 생성 방법, 텍스트 수식을 이용한 방법, 그리고 독자들이 제안한 더 간결한 수식까지 모두 다룹니다.
1) B열에 직원들의 이메일 주소를 채워 넣고자 합니다. 회사 내부 IT 부서가 이메일 주소를 만드는 규칙은 직원의 이름(first name)과 성(last name)을 회사 도메인과 결합하는 방식입니다.
2) 먼저 B5 셀에 플래시 필에게 보여줄 예시를 입력해야 합니다. 플래시 필은 이 예시를 템플릿 삼아 나머지 열을 채워 줍니다. 다음 텍스트를 입력합니다:
admin@wsxdn.com
그런 다음 CTRL+ENTER 키를 누릅니다.
3) CTRL+ENTER를 누르면 Excel이 입력한 텍스트를 자동으로 하이퍼링크로 변경합니다.
4) 이제 이메일 주소로 채울 전체 범위 B5:B15를 선택한 후, 플래시 필 단축키인 CTRL+E를 누르거나, 범위를 선택한 상태에서 [데이터] 탭으로 이동해 [플래시 필]을 클릭합니다.
5) 나머지 범위도 필요한 이메일 주소들로 자동으로 채워집니다.
더 읽기: 플래시 필로 엑셀 텍스트 나누기
TEXT 수식 활용하기
Excel 구버전을 사용 중이라 해도 걱정할 필요 없습니다. TEXT 수식으로 동일한 작업을 수행할 수 있기 때문입니다. 다만 이 경우에는 TEXT 수식을 사용하는 편이 조금 더 복잡합니다.
1) 우선 A열의 텍스트를 살펴보고 어떤 텍스트 함수를 사용해야 할지 파악합니다.
2) 이름(first name) 뒤에 공백 하나가 오고, 그다음 성(last name)이 있는 구조임을 알 수 있습니다.
3) 이제 수식을 처음부터 하나씩 만들어 보겠습니다.
가장 먼저 SEARCH 함수를 사용해 공백의 위치를 찾습니다. B5 셀에 다음을 입력합니다:
=SEARCH(" ", A5, 1)
CTRL+ENTER를 누릅니다.
4) CTRL+ENTER를 누르면 결과값 5가 반환됩니다. 즉, Emma Smith의 경우 공백이 다섯 번째 문자라는 뜻입니다. 수식을 아래로 복사하면 다른 직원들의 공백 위치도 확인할 수 있습니다. 예를 들어 Nicole Roberts의 경우 공백이 일곱 번째 문자에 위치합니다.
5) 이제 LEFT 함수를 사용해 이름(first name)만 추출할 수 있습니다. 공백 위치를 기준으로 공백 왼쪽의 모든 문자를 가져오면 이름이 됩니다.
LEFT 함수에는 추출 대상 텍스트 문자열과, 왼쪽에서부터 몇 글자를 추출할지 숫자를 지정해야 합니다.
A5 셀을 텍스트 문자열로 지정하고, 이름만 추출하기 위해 필요한 글자 수는 SEARCH 함수로 찾은 공백 위치에서 1을 빼면 됩니다:
=LEFT(A5, SEARCH(" ", A5, 1) - 1)
공백은 포함하지 않으므로 1을 빼주는 것입니다.
6) CTRL+ENTER를 누른 뒤 수식 셀을 더블클릭해 아래로 복사하면 모든 이름(first name)이 추출됩니다.
7) 이제 CTRL+C를 두 번 눌러 클립보드를 열고 수식을 복사해 둡니다.
8) 수식이 저장되었으므로 B열 내용을 지우고 성(last name) 추출 작업으로 넘어갑니다.
9) 공백 위치를 이미 알고 있고, 이름은 LEFT 함수로 추출했으니 성은 RIGHT 함수로 추출하면 됩니다. 성을 추출하는 수식을 만들어 보겠습니다.
10) RIGHT 함수에는 오른쪽에서부터 추출할 문자 수를 알려줘야 합니다. 공백 위치를 이미 알고 있으므로, LEN 함수로 전체 문자 수를 구한 뒤 SEARCH 함수가 알려준 공백 위치를 빼면 됩니다. 다음 수식을 사용합니다:
=LEN(A5) - SEARCH(" ", A5, 1)
이렇게 하면 공백 이후 남은 문자 수를 구할 수 있습니다.
11) 이제 위 수식과 RIGHT 함수를 결합해 성을 추출합니다. 오른쪽에서 몇 글자를 가져와야 하는지 이미 알고 있기 때문입니다. 다음 수식을 입력합니다:
=RIGHT(A5, LEN(A5) - SEARCH(" ", A5, 1))
13) 수식을 복사한 뒤 셀 내용을 지웁니다.
14) 이제 각 단계의 로직을 확인하고 클립보드에 복사해 두었으니, 모든 요소를 하나의 큰 수식으로 합칠 차례입니다. 열을 지운 후 B5 셀에 다음 수식을 입력합니다:
=LEFT(A5, SEARCH(" ", A5, 1) - 1)&RIGHT(A5, LEN(A5) - SEARCH(" ", A5, 1))&"@mycompany.com"
15) 수식 셀을 더블클릭해 아래로 복사하면 나머지 열도 이메일 주소로 채워집니다.
똑같은 효과를 내는 훌륭한 독자 제안 수식
Rahul Singh님이 훨씬 간단한 수식으로 동일한 효과를 얻는 방법을 제안했습니다:
B5 셀에 다음 수식을 입력합니다:
=CONCATENATE(SUBSTITUTE(A5," ",""),"@mycompany.com")
Karthik님도 앞서 소개한 텍스트 수식과 같은 결과를 내는 더 간결한 수식을 제안했습니다:
=SUBSTITUTE(A5," ","")&"@mycompany.com"
더 읽기: 엑셀에서 플래시 필로 데이터 분할하기 (4가지 빠른 방법)
작업 파일
결론
플래시 필은 Excel 2013 이상 버전에서 사용할 수 있는 매우 편리한 기능입니다. 향상된 AutoFill이라 할 수 있으며, 작업 시간을 크게 절약해 줍니다. 하지만 구버전 Excel 사용자이거나 플래시 필가 작동하지 않는 특정 상황에서는 TEXT 수식이 훌륭한 대안이 됩니다. 수식 작성이 다소 복잡하긴 하지만, 어떤 복잡한 패턴도 처리할 수 있다는 장점이 있습니다. 여러분은 데이터를 추출하거나 서식을 지정할 때 플래시 필과 TEXT 수식 중 무엇을 선호하시는지, 그 이유는 무엇인지 댓글로 알려주세요.
관련 글
- 엑셀에서 플래시 필 끄는 방법 (2가지 쉬운 방법)
- [해결!] 엑셀에서 플래시 필이 작동하지 않을 때 (5가지 원인과 해결책)
- 엑셀에서 플래시 필이 패턴을 인식하지 못할 때 (4가지 원인과 수정 방법)