[엑셀 함수] 원하는 문자(텍스트)에서 자르기

 엑셀에서 원하는 문자에서 자료를 끊어야 할 때가 있습니다. 오늘은 특정 문자, 내가 원하는 문자에서 문자를 자르는 방법에 대해 알아보겠습니다.

1. 특정 문자가 하나만 존재하는 경우

 아래와 같이 행정구역 자료를 가지고 올 때, 시도만 가져오거나 시군만 가져오는 방법에 대해 알아보겠습니다.

 첫 번째, 시도만 가져오는 방법입니다. 시작은 FIND 함수로 시작됩니다. "띄어쓰기"의 위치를 확인하고, LEFT 함수를 써서, 해당 위치까지의 문자열을 반환합니다.

 두번째, 시군구만 가져오는 방법입니다. 이것도 시작은 FIND 함수로 시작됩니다. 이후에 MID 함수나 RIGHT 함수를 사용해서 결과를 반환할 수 있습니다. 우선 MID 함수를 사용하면, 시작 위치와 길이를 잘 정의해야 합니다. 위치나 길이는 결과보고 조정하셔도 괜찮습니다.

 RIGHT 함수는 길이만 정의하면 되기 때문에 MID 함수보다 조금 심플합니다. 길이도 결과를 보고 조정하면 되겠습니다.

 시군구를 가져올 때, 이런 경우가 있습니다. 나는 끝에 있는 행정구역만 가지고 오고 싶다. 위의 함수를 사용하면, "경기도 수원시 장안구"는 "수원시 장안구"를 반환합니다. 근데... 마지막 "장안구"가 나왔으면 좋겠다는 경우가 있습니다. "서울특별시 종로구"에서는 "종로구"만, "경기도 수원시 장안구"에는 "장안구"만 가져오고 싶다. 이 경우는 쉬워 보이지만 사실은 조금 복잡한 과정을 거쳐야 합니다.

 

2. 특정 문자가 2개 이상 존재하는 경우

 특정 문자가 2개 이상 존재하는 경우에 내가 원하는 문자까지 가지고 올때에는 조금 복잡한 과정을 거쳐야 합니다. 이때, 사용하는 것이 지난번 포스팅 때 사용한 SUBSTITUTE 함수입니다.

stat-and-news-by-daragon9.tistory.com/247

[엑셀 함수] 문자(텍스트) 바꾸기: REPLACE, SUBSTITUTE

 아래 함수를 사용하면, 마지막 어절을 가지고 올 수 있습니다. 이 문장을 풀어보도록 하겠습니다.

 이 함수는 마지막(순서) "띄어쓰기"(특정문자) 이후의 문자를 자르는 함수입니다.

 "서울특별시"는 적절한 예시가 아니니 두번째 "서울특별시 종로구"를 예시로 들어보겠습니다.

=RIGHT(B4,LEN(B4)-FIND("A",SUBSTITUTE(B4," ","A",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))

 이걸 분해하기 위해서는 거꾸로 가야 합니다. 함수를 만들 때에는 마지막에 사용된 SUBSTITUTE 함수부터 써야 되지만, 이해하기 위해서는 마지막부터 가야 합니다.

 

 첫번째는 RIGHT함수입니다.

=RIGHT(B4,LEN(B4)-FIND("A",SUBSTITUTE(B4," ","A",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))

 RIGHT 함수는 문자를 오른쪽 끝에서 자르는 함수입니다. FIND 뒤의 내용을 줄이면 아래와 같이 표현할 수 있습니다. 아래 함수의 결과로 "종로구"를 가지고 옵니다.

=RIGHT(B4,LEN(B4)-J4)

"서울특별시 종로구"를 오른쪽에서 3(=9-6)만큼 자르세요.

 그럼, J4가 가리키는 FIND함수를 봅시다.

=RIGHT(B4,LEN(B4)-FIND("A",SUBSTITUTE(B4," ","A",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))

 FIND함수는 특정 문자의 위치를 가리키는 함수입니다. SUBSTITUTE 뒤의 내용을 줄이면 아래와 같이 표현할 수 있습니다. 아래 함수의 결과로 6을 가지고 옵니다. 근데 갑자기 "A"는 뜬금없이 등장하는데... 사실 "A"든 "ㄱ"이든 상관은 없습니다. 우리가 자르기로 결정한 위치에 문자를 정의하기 나름이니깐요. H4를 만들기 위해서, 그 위치에 문자를 어떻게 삽입하느냐... 가 다음 함수입니다.

=FIND("A",H4)

"서울특별시A종로구"에서 "A" 위치를 알려주세요.

 H4는 SUBSTITUTE 함수로 만들어집니다. 이게 두 번째 SUBSTITUTE입니다.

=RIGHT(B4,LEN(B4)-FIND("A",SUBSTITUTE(B4," ","A",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))

 H4는 SUBSTITUTE 함수로 만들어집니다. 이게 두 번째 SUBSTITUTE입니다. LEN 뒤의 내용을 줄이면 아래와 같이 표현할 수 있습니다. SUBSTITUTE는 원하는 순서의 원하는 문자를 다른 문자로 바꾸는 함수입니다. 아래 함수의 결과로 "서울특별시 종로구"는 "서울특별시A종로구"가 됩니다. 다른 예를 보면, "경기도 수원시 장안구"는 "경기도 수원시A장안구"가 됩니다. 그럼 원하는 순서, 우리는 마지막 "띄어쓰기"를 구하는 F4를 어떻게 찾을 것인지가 나와야 하는데... 이게 우리가 하려는 함수의 핵심입니다.

=SUBSTITUTE(B4," ","A",F4)

"서울특별시 종로구"에서 첫 번째(F4=1) "띄어쓰기"(" ")를 "A"로 바꾸세요.

 F4는 LEN 함수로 만들어집니다. LEN 함수는 문자의 길이를 구하는 간단한 함수입니다. 첫 번째 SUBSTITUTE 함수를 줄이면,

=RIGHT(B4,LEN(B4)-FIND("A",SUBSTITUTE(B4," ","A",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))

=LEN(B4)-LEN(D4)

"서울특별시 종로구"의 길이에서 "서울특별시종로구"의 길이의 차이를 구하세요.

 함수의 결과로 두 문자의 차이는 1이 됩니다. 우리가 원하는 문자가 몇 개 있는지 알 수 있는 방법인데요. 특정 문자의 개수를 세는 방법은 특정문자를 없애서 그 길이의 차이를 특정문자의 길이로 나누면 알 수 있습니다. 마지막 SUBSTITUTE 함수와 같이 설명하겠습니다.

=RIGHT(B4,LEN(B4)-FIND("A",SUBSTITUTE(B4," ","A",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))

 SUBSTITUTE 함수를 사용하여, "서울특별시 종로구"에서 "띄어쓰기"를 없애서, "서울특별시종로구"를 만들고, 두 문자의 차이를 보면 "띄어쓰기"의 개수를 알 수 있습니다. 다른 예시를 보면, "경기도 수원시 장안구"는 띄어쓰기가 2개 있습니다. SUBSTITUTE 함수를 사용해서 "띄어쓰기"를 없애면, 전체 길이에서 두 번의 "띄어쓰기"가 줄어든 "경기도수원시장안구"가 됩니다.

 그럼 전체 함수를 풀어서 설명해 보겠습니다.

=RIGHT(B4,LEN(B4)-FIND("A",SUBSTITUTE(B4," ","A",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))

"서울특별시 종로구"의 "띄어쓰기"를 없애서 "띄어쓰기"의 개수를 구하고, 가장 마지막 "띄어쓰기"를 "A"로 바꾼 후, 오른쪽 끝에서 "A"까지 자르세요.

문장이 엄청 길어졌습니다. 이런 거 쓰는 날이 올까 싶지만... 그런 날이 있더라고요. 아예 없지는 않습니다. ㅎㅎㅎ 이런 건 함수를 안다고 사용할 수 있는 게 아니더라고요. 그냥 아이디어만 알고 가시면 좋을 거 같습니다. 하나의 문자열에서 내가 원하는 문자가 몇 개 있을까?를 알아내는 게 핵심입니다. 내용이 길어졌는데... 이상으로 원하는 문자에서 자르는 방법을 마치겠습니다.