지번 주소인 경우 마지막에 35-6 또는 산35-6처럼 지번이 오게 되는데, 이것을 추출해 보겠습니다.
1. Mid와 Find함수를 사용하기
주소가 중간에 공백이 있으므로 공백의 위치를 찾아 계속 그다음 문자를 찾으면 마지막에 남는 것이 지번이 됩니다.
가. Find, Findb 함수
(1) 첫 번째 공백 찾기
공백을 찾는 것은 Find함수를 사용하는데,
구문은 FIND(find_text, within_text, [start_num])이고,
find_text는 찾는 텍스트, within_text는 찾을 대상 텍스트이고, start_num은 옵션인데 시작 위치를 말합니다.
위의 경우 첫 번째 공백은 B2셀에
=find(" ",a2) 라고 입력하면 되는데,
공백 한 칸을 찾아야 하므로 ""이 아니라 " "라고 중간에 공백이 하나 있도록 입력해야 합니다.
그러면 숫자 6이 구해집니다.
한글도 2바이트가 아니라 1로 계산합니다. 따라서, 서울특별시 5글자에 공백이 있으니 6번째가 되는 것입니다.
그러나 Findb함수를 사용하면 바이트 단위로 계산하므로 한글이 2바이트가 돼서 11이 구해집니다.
(2) 두 번째 공백 찾기
두 번째 공백은 첫 번째 공백 다음부터 찾아야 하므로 start_num을 첫 번째 공백 + 1로 주면 됩니다.
따라서, 수식은 =find(" ", a2, find(" ",a2) + 1)
이라고 입력하는데 보기 좋도록 중간에 공백을 추가했습니다.
한글 7자에 공백이 2개 있으니 9가 나오는 것입니다. start_num(시작 위치)과 관계없이 처음부터의 위치를 구해줍니다.
(3) 세 번째 공백 찾기
세 번째 공백은 두 번째 공백 + 1부터 찾아야 합니다.
따라서 수식은 =find(" ", a2, find(" ", a2, find(" ",a2) + 1) + 1)
나. Mid 함수
(1) 구문
MID(text,start_num,num_chars) 인데,
text은 문자열, start_num은 시작위치, num_chars는 문자의 개수입니다.
예를 들어 위 예제에서 서울특별시를 추출하려면
=mid(a2, 1, 5)가 됩니다.
(2) 지번 추출하기
세 번째 공백 위치 + 1부터 문자의 개수를 쓰는데, 숫자를 정확하게 입력하지 않고 크게 지정해도 무방합니다.
따라서, 수식은
=mid(a2, find(" ", a2, find(" ", a2, find(" ",a2) + 1) + 1) + 1, 10)
이 됩니다.
find(" ", a2, find(" ", a2, find(" ",a2) + 1) + 1) + 1에서
find(" ", a2, find(" ", a2, find(" ",a2) + 1) + 1)까지는 세 번째 공백의 위치이고,
그다음부터 지번을 가져와야 하기 때문에 여기에 + 1이라고 1을 더한 것입니다.
다. Find, Mid 함수 사용 시 문제점
위와 같이 수식을 사용하는 것은 공백이 3개 있을 때로 공백이 2개 있다면 에러가 발생합니다.
A1셀에서 예장동 이후만 복사한 후 A3셀에 붙여 넣고, B2셀의 채우기 핸들을 아래로 끌어서 수식을 복사하면 공백이 2개이기 때문에 에러가 발생합니다. 그리고, Find함수가 너무 많아 알아보기도 어렵습니다.
2. Lambda 함수 사용하기
Lambda함수는 Microsoft 365용 Excel, Microsoft 365용 Excel(Mac용), 웹용 Excel에서만 이용가능합니다. 따라서 이전 버전이란 함수 목록에 표시되지 않고, Lambda함수로 된 수식은 #NAME! 에러가 발생합니다.
가. 구문
=LAMBDA([parameter1, parameter2, ...,] 계산식)
로 매개 변수를 넣고, 이를 이용해서 계산식을 만든 후 그 결괏값을 반환합니다.
사용법은
① 수식 입력 줄에 바로 Lambda함수를 입력할 수도 있고,
② 이름관리자를 이용해 이름에 Lambda 함수식을 지정하는 방식 2가지가 있습니다.
나. 수식 입력줄에 입력
수식은
=LAMBDA(within_text,FIND(" ",within_text))(A5)
라고 입력하는데,
find_text를 매개변수로 설정하고
이를 이용해서 FIND(" ",within_text) 라고, within_text에서 공백 한 칸을 찾는 계산식을 입력한 다음
끝 부분의 괄호 안에 within_text값으로 A5를 대입합니다.
그러면, =find(" ",A5)와 같은 수식이 돼서 6이 반환됩니다.
다. 이름 관리자를 이용하는 방법
(1) 이름관리자에 Lambda함수식 입력하기
수식 탭에서 이름 관리자 명령을 클릭합니다.
이름 관리자 창이 열리면 새로 만들기 버튼을 누른 후
이름에 blank_pos라고 입력하고
참조 대상에 =LAMBDA(within_text,FIND(" ",within_text)) 라고 입력값을 빼고 입력합니다.
그러면 아래와 같이 되는데, 확인 버튼을 누릅니다.
그러면 blank_pos란 이름이 하나 생기는데, 사용자 정의함수처럼 사용가능합니다.
닫기 버튼을 누릅니다.
C5셀에 =blank_pos(a5)라고 입력하면 사용자 정의 함수처럼 작동하고 B5셀과 같은 값 6이 반환됩니다.
(2) Lambda함수 재귀 호출로 입력하기
참조대상에서 이름을 Lambda 함수식에 대입하면 이름이 계속 호출되므로 여러 번 반복 실행이 가능합니다.
참조대상에 아래와 같이 작성할 수 있습니다.
=LAMBDA(within_text,
IF(iserror(find(" ",within_text)), within_text,
blank_pos(
mid(within_text,find(" ",within_text)+1,100)
)
)
)
Lambda함수의 인수로 within_text를 지정하고,
If부터 계산식이고,
iserror(find(" ",within_text))가 조건식인데 문자열에 공백 한 칸이 있는지 찾아서 없다면이 되고,
조건식이 참이면 within_text를 반환합니다. 한 칸 공백이 없는 경우란 끝에 있는 지번이 됩니다.
조건식이 거짓이면 아래 식이 실행되는데,
blank_pos(
mid(within_text,find(" ",within_text)+1,100)
)
blank_pos란 이름(사용자 정의 함수)과 같으니까 blank_pos를 반복 실행해서 공백 뒷부분만 반환하고, 결국 공백이 없어지면 공백이 없는 within_text인 지번을 반환하고 끝나는 것입니다.
mid(within_text,find(" ",within_text)+1,100)는 공백 한 칸 다음부터 문자열 100개를 가져오는 것입니다. 위에서는 세 번째 공백부터 문자열을 가져오므로 10을 썼는데, 첫 번째 공백 이후의 문자열을 모두 가져와야 하므로 숫자를 크게 했습니다.
위 수식을 복사한 다음, 이름 관리자를 실행하고 편집 버튼을 누른 다음
참조 대상 오른쪽 화살표 키를 누르고,
아래 화면이 나오면 Ctrl + V키를 눌러 수식을 붙여 넣습니다.
그런 다음 오른쪽 화살표를 눌러 참조대상 입력 상자를 닫습니다.
그러면 이름 관리자 창으로 돌아오는데 닫기 버튼을 누릅니다.
'이름 참조의 변경 내용을 저장하시겠습니까?'하고 묻는데 예를 누릅니다.
그리고, C5셀을 보면 6이 산5-85로 값이 바뀌었습니다.
blank_pos란 이름이 사용자 정의 함수처럼 사용됩니다.
C6셀로 채우기 핸들을 끌면 공백이 2개인 경우에도 지번을 잘 가져옵니다.
라. 수식 입력줄과 이름 관리자의 참조 대상의 차이점
| 수식 입력줄 | 참조 대상 |
| 입력 줄을 Alt + Enter키를 사용해 여러 줄로 입력 가능 | 한 줄로만 표시됨 |
| 에디터에서 Enter 키 또는 Tab키로 입력한 것을 복사해서 붙일 경우 에러 발생 | 에디터에서 Enter 키 또는 Tab키로 입력한 것을 복사해서 붙이더라도 에러 발생하지 않음 |
| 수식 입력 줄은 높이 조절 등으로 편집하기 쉬움 | 참조 대상 입력 줄은 높이 조절이 안되고 폭만 조절할 수 있어서 편집하기 어려움 |
| 에디터에서 작업한 후 엔터 값과 탭 값을 대체 후 복사해서 붙여넣어야 | 에디터에서 작업 후 복사해서 붙여넣는 것이 좋음 |
(참조 대상의 입력 줄의 높이는 조절되지 않고 길이만 조절 가능)
'Excel' 카테고리의 다른 글
| Lambda 함수 매개변수 2개 이상인 경우(2) (0) | 2023.01.19 |
|---|---|
| Lambda 함수 매개변수 2개 이상인 경우(1) (0) | 2023.01.18 |
| 수식을 이해하기 쉽게 만들기(1) - 수식 입력줄 높이 조절, Notepad++ 사용 (0) | 2023.01.16 |
| Aggregate(옵션 적용 집계) 함수(5) - 조건이 여러 개인 경우 (2) | 2023.01.11 |
| Aggregate(옵션 적용 집계) 함수(3) - Index, Row, Code, Char 함수와 결합 (0) | 2023.01.09 |