엑셀 특정 문자 뒤에 있는 문자 추출하기

회사에서 엑셀을 통해 데이터를 취합받는 경우가 많이 있습니다.
이럴 때 소속, 직급, 이름 등을 구분하지 않으면 한 셀에 모든 데이터를 기입하는 경우가 자주 발생합니다. 이럴 경우 데이터를 다시 정리해야 하는 비효율이 생기고 불필요한 업무를 두 번 해야 합니다.

2가지 방법을 활용할 수 있습니다.

 

텍스트 나누기 사용

한 셀에 모든 내용이 포함되어 있더라도 규칙적으로 되어 있다면 텍스트 나누기를 사용하여 쉽게 해결할 수 있습니다.

1. 텍스트를 나눌 범위(B2:B11)를 선택합니다.

2. [데이터] - [데이터 도구]그룹에 텍스트 나누기를 클릭합니다.

• 텍스트 마법사가 나타나고 입력된 형태에 맞게 클릭하여 줍니다.
• 현재 데이터는 너비가 일정하지 않고 구분 기호(콜론, 공백)로 분리되어 있습니다. 따라서 파일 유형을 구분 기호로 분리됨을 선택하고 다음을 클릭합니다.
• 적합한 구분 기호를 선택하여 클릭합니다. 공백이 내용마다 포함되어 있기에 공백을 클릭합니다. 만약 콜론 등으로 구분되어 있다면 기타를 선택하고 콜론을 입력하면 콜론을 기준으로 구분선이 표시됩니다. 마침을 클릭합니다.
• B2셀에 있던 텍스트들이 G열까지로 나누어졌습니다. 불필요한 내용은 삭제한 후에 사용하시면 됩니다. 이 경우는 데이터가 복잡하지 않고 데이터가 많지 않을 때 사용하면 좋습니다.

MID, RIGHT, LEN, FIND 함수 사용

함수 사용은 조금 많이 복잡합니다.

그렇기에 굳이 이해하지 않으셔도 되고 수식을 그대로 상황에 맞게 변경하여 사용하는 것을 추천드립니다.

우선 기본적으로 사용하는 함수에 대해 설명드립니다. 간단하게 설명드리며 자세한 내용은 함수를 클릭하여 확인 바랍니다.

• MID : 셀의 지정된 위치로부터 지정된 수만큼 문자를 반환합니다.
• RIGHT : 특정 위치의 데이터를 추출하는 함수입니다.
• LEN : 텍스트의 개수를 세는 함수입니다.
• FIND : 대소문자를 구분하여 특정 문자의 위치를 찾아주는 함수입니다.

함수를 사용하여 B2셀의 소속, 이름, 직급을 추출하겠습니다.

소속과 이름은 MID 함수를 사용하고 직급은 RIGHT 함수를 사용하겠습니다.

1. 소속과 이름

D2셀에 아래와 같이 입력합니다.

=MID(B2, FIND("소속: ", B2)+LEN("소속: "), FIND("이름: ", B2)-FIND("소속: ", B2)-LEN("소속: "))

• 이 함수를 처음부터 만들기는 쉽지 않습니다. 따라서 최종 결과값을 생각하면서 역순으로 만들어야 합니다.
• 찾으려는 값은 소속인 '영업1팀'으로 함수로는 MID(B2, 5, 4)입니다.
  즉, B2셀의 5번째부터 4개의 문자를 추출하게 됩니다. 하지만 여기서는 '영업1팀'과 '총무팀' 등 문자의 개수가 다르기에 뒤에 공백까지 포함하고 수식 적용이 편한 MID(B2, 5, 5)로 작성하였습니다.
  - 5번째부터의 값, 즉 5를 구하기 위해서 FIND("소속: ", B2)의 값 1과 LEN("소속: ")의 값 4를 더했습니다.
  - 5개의 값을 추출하기 위해 FIND("이름: ", B2)-FIND("소속: ", B2)-LEN("소속: ")을 적용하여 5를 구했습니다.
• 텍스트 나누기를 사용하시기 바랍니다.
  그 많은 함수 중 굳이 FIND, LEN 함수를 사용해야 하는지, 개수를 어떻게 더하고 뺄 생각을 했는지 모두 이해하기는 어렵습니다. 또한 데이터의 띄어쓰기, 기호 등이 달라지면 또 함수를 고민해야 합니다. 비효율적입니다.

이름은 아래의 함수로 구할 수 있습니다.

=MID(B2, FIND("이름: ", B2)+LEN("이름: "), FIND("직급: ", B2)-FIND("이름: ", B2)-LEN("이름: "))

 

2. 직급

직급은 RIGHT 함수를 사용하여 구할 수 있습니다. D2셀에 아래와 같이 입력합니다.

=RIGHT(B2, LEN(B2)-FIND("직급: ",B2)-LEN("직급: ")+1)

• 22개의 값(LEN(B2))에서 직급의 위치값 17(FIND("직급: ",B2)을 빼고 다시 문자 수 4(LEN("직급: ")를 빼서 1의 값을 구하였습니다. 이때 결과값은 제일 마지막 글자인 '장'입니다. 그래서 숫자 1을 추가하여 RIGHT(B2, 2)로 2자리 값인 '과장'을 추출하였습니다.

이상 엑셀 특정 문자 뒤에 있는 문자 추출하는 방법에 대해 설명하였습니다.

왠만하면 위의 함수는 사용하지 않는 것이 좋습니다. 너무 많은 시간이 필요합니다.

감사합니다.

 

함께 읽으면 좋은 글

데이터 채우기 2와 텍스트 나누기

데이터 채우기 1

엑셀 데이터 입력 시 주의사항

특정 위치의 데이터 추출 함수 LEFT, RIGHT

문자 위치를 찾는 FIND 함수

문자의 자릿수를 세는 LEN함수와 표시 형식을 바꾸는 TEXT함수