이번 시간에는 문자열에서 일부를 추출하고 대체하고, 위치를 표시하고 등등에
관한 모든 함수들에 대해 다루어보겠습니다!
제일 먼저 함수들을 살펴보기 전에 문자열의 길이를 알아보는 아주 간단한
함수를 알아봅시다!
LEN이라는 함수인데요! 영어단어 LENGTH가 ‘길이’라는 뜻을 가지고 있는 것
아시죠?
LEN(대상) 이렇게 사용하면 문자열의 길이를 알 수 있습니다!
예를 들어볼게요!
이렇게 사용하면 되는데요! 결과값을 보죠!
여기서 눈치 채신 분들도 있을 텐데 LEN 함수는 공백을 포함합니다!
우리가 보기엔 A2와 A3의 길이는 같아보이지만 A3에는 앞에 공백이 두개가 있기 때문에 길이가 2만큼 더 긴 것이죠!
혹시 공백을 없애고 싶다면 TRIM함수를 쓰면 된답니다.
이렇게 말이죠!
① LEFT, RIGHT, MID 함수
텍스트에서 몇 개의 문자만 추출하는 함수로는 LEFT, RIGHT, MID 함수가 있는데요!
하나하나 살펴보도록 하죠!
LEFT(대상 텍스트, 필요 문자 개수): 대상 텍스트에서 필요한 문자 개수만큼 왼쪽에서 추출
RIGHT(대상 텍스트, 필요 문자 개수): 대상 텍스트에서 필요한 문자 개수만큼 오른쪽에서 추출
MID(대상 텍스트, 시작 위치, 필요 문자 개수): 대상 텍스트에서 원하는 시작 위치에서 필요한 문자 개수만큼 문자 추출
예제를 보면 확실히 이해가 갑니다! 보시죠!
이런 데이터가 있다고 가정해봅시다!
‘관리번호’열에 사용된 문자 데이터가 굉장히 길죠?
관리번호 일부인 2020, 2019 등의 연도 숫자에 해당하는 앞 부분만 추출해오고 싶으면 어떻게 할까요?
그럴 땐 LEFT함수를 사용하면 된답니다!
왼쪽에서부터 4개를 뽑아야 하므로
함수를 이렇게 사용하면 되겠죠? 그럼 결과를 보시죠!
연도에 해당하는 일부 문자열만 추출되었네요!
반대로 0002, 0001과 같은 뒤의 문자열만 추출하고 싶다면 어떻게 할까요?
당연히 RIGHT 함수를 쓰면 되겠죠?
그러면 뒤에서 네번째까지 문자열이 추출됩니다!
이제는 ‘강원강릉’이라는 중간의 지역에 해당하는 문자열만 추출하고 싶네요!
이럴 때 바로 MID 함수를 쓰는 것이죠!
MID 함수는 LEFT, RIGHT 함수와 다르게 인수가 한 가지 더 있어요!
도대체 중간 어디서부터 뽑으란 말이야? 라는 궁금점을 방지하기 위해
시작하는 위치를 인수로 집어넣습니다!
‘강원강릉’을 뽑으려면 6번째부터 시작해야겠죠? 그리고 4개의 문자열을 뽑아야합니다
그래서
이렇게 함수를 입력해주면
원하는 문자열만 추출되었죠?
조금만 연습해보면 어렵지 않아요!!!
② REPLACE 함수
그럼 이제 문자열을 대체해볼까요?
위의 데이터에서 관리번호 끝에 해당하는 0002, 0001, 0014 따위의 번호가 유출되면 안되는 번호라고 가정해봅시다!
그러면 저 4개의 문자열을 **** 따위의 문자열로 바꿔 주어야 하지 않을까요?
마치 주민등록번호 뒷자리를 * 기호로 대체하는 것처럼 말이죠!
그럴 땐 REPLACE 함수를 사용합니다!
REPLACE(기존 문자, 시작 위치, 바꿀 문자 개수, 새로 넣을 문자)
기존 문자에서 시작 위치로 정한 위치부터 원하는 개수를 다른 문자로 대체하는 함수
이렇게 사용하는 것이죠!
0002는 관리번호 전체 텍스트에서 11번째부터 시작하고 4개의 문자열로 이루어져 있으니
시작 위치 인수로는 11, 바꿀 문자 개수 인수로는 4, 새로 넣을 문자로는 “****”를 입력하는 것입니다!
이렇게요!!
그러면 결과를 한 번 보시죠!
이렇게 관리번호 뒷자리가 “*”문자로 대체되어 보안기능을 가졌네요! 이렇게 관리한
관리번호를 원래 관리번호 자리에 붙여넣기 하면 되겠죠!
③ SUBSTITUTE 함수
앞서 배운 REPLACE 함수처럼 SUBSTITUE도 다른 문자로 대체하는 함수지만
조금 다른데요
REPALCE 함수는 몇 번째부터 몇 개의 문자를 다른 문자로 바꾸지만,
이 함수는 원하는 문자를 찾아서 다른 문자로 대체합니다
SUBSTITUE(기존 문자, 찾을 문자, 새로 넣을 문자, 시작 위치)
이렇게 사용하는데요!
예제를 봅시다!
운영상태를 정상영업이 아닌 정상운영으로 바꾸고 싶다면!
앞서 배운 REPLACE 함수를 써도 되긴 하지만
SUBSTITUE 함수를 써서 바꿔볼게요
그런데 방금 SUBSTITUE 함수는
SUBSTITUE(기존 문자, 찾을 문자, 새로 넣을 문자, 시작 위치)
이런 공식을 가지고 있다고 했는데 제가 ‘시작 위치’에 해당하는 인수를 설정하지 않았죠?
이 인수는 대상 셀에 입력된 문자열에서 찾는 문자가 여러 개 있을 경우에만 사용합니다
여러 개 있을 경우 몇 번째 위치한 문자를 바꿀 것인가를 위한 인수이죠!
만약 운영상태에 이런 문자 데이터가 있는데
‘영업상태: 정상운영’이라고 바꾸고 싶다고 가정해보죠!
이때는 ‘영업’이라는 텍스트가 두개나 있는데 두번째 있는 ‘영업’ 텍스트를 ‘운영’으로
바꾸고 싶은 거잖아요! 그럴 때 바로 시작 인수를 2로 지정해주는 겁니다
이렇게요! 그러면 결과를 볼게요!
앞의 ‘영업’은 그대로 ‘영업’으로 유지되면서 뒤의 ‘영업’은 ‘운영’으로 바뀌었네요!
④ REPT 함수
특정 문자를 원하는 횟수만큼 반복해서 표시하고 싶을 땐 어떻게 하나요?
무슨 말인지 잘 모르겠다고요?
예를 들면
이런 데이터가 있다고 가정해볼게요
재고가 얼마나 있는지 문자를 통해 좀 더 직관적으로 파악하고 싶을 때!
이럴 때 REPT 함수를 쓰는 것입니다!
REPT(대상 문자, 반복 횟수)
지정한 특정 문자를 원하는 횟수만큼 반복해서 표시하는 함수
10개 단위마다 ● 기호를 써주고 싶은 거죠!
결과를 보면
이렇게 남은 재고를 직관적으로 볼 수 있죠!
⑤ FIND, SEARCH 함수
이제는 문자열에서 특정 문자의 위치를 찾아볼게요!
FIND(찾을 문자,비교 대상,시작 위치)
SEARCH(찾을 문자,배교대상,시작 위치)
FIND와 SEARCH 함수가 같죠? 차이점은 이렇습니다!
FIND 함수는 영문자의 대소문자를 구분해서 찾고 (*,?)와 같은 기호 사용 불가
SEARCH 함수는 영문자의 대소문자를 구분하지 않고 (*,?)와 같은 기호 사용 가능
법인또는상호에 해당하는 데이터에서 괄호가 있는 위치를 찾아볼게요!
괄호가 없는 데이터는 오류로 뜨겠죠?
인수에 대해 살펴보면
find_text는 찾을 텍스트, 그러니까 “(“를 입력한 것이고요!
within_text는 비교 대상 텍스트
start_num은 1을 입력하여 맨 앞에서부터 “(“를 찾도록 지정하는 것입니다!
⑥ INDEX 함수
지정한 데이터 영역에서 원하는 행 수와 열 수에 위치한 셀 값을 찾는 함수도 있습니다!
바로 INDEX 함수인데요!
INDEX(비교 대상, 행 수, 열 수) 이렇게 사용합니다.
밑의 예제를 보시죠!
지역 행과 코드번호 행을 채우고 싶은데
가져올 행의 위치가 정해져있네요!
이럴 때는 INDEX 함수를 사용하면 간편합니다
이 함수를 쓸 건데요! 참조할 대상을 G1부터 H6으로 정하고
가져올 행 위치에 입력된 수를 행 숫자로
지역에 해당하는 값을 가져오고 싶으니 열 숫자를 1로 정하면 됩니다!
G1부터 H6까지의 범위에서는 지역이라는 행이 1이고 코드번호라는 행이 2번째 행이니까요!
결과를 볼까요?
밑의 행들에도 수식을 복사해줄 것이므로 범위를 절대 참조로 고정해두었답니다!
(전 게시물에 절대 참조에 관한 설명이 있습니다!)
⑦ ROW, COLUMN 함수
위의 INDEX 함수에서 행과 열에 관한 이야기가 나왔으니 ROW, COLUMN 함수를 알아보죠!
ROW 함수는 지정한 셀이 위치한 행 수를 알아볼 때 사용합니다
COLUMN 함수는 지정한 셀이 위치한 열 수를 알아볼 때 사용하구요!
“서울”이라는 텍스트는 G4열에 위치하니까 ROW(G4)는 4로
COLUMN(G4)는 7로 나오겠죠?
⑧ MATCH 함수
마지막으로 MATCH 함수는 특정 셀에 위치한 값이 지정한 범위 내에서 몇 번째에 위치하고 있는지 알아보는 함수식입니다!
예를 통해 확실히 알아보죠!
C6에 위치한 수원이라는 값이 A8:A13에서 몇번째에 위치하는지 찾고 싶은 것이죠!
결과값으로는 5가 나오겠죠?
이상으로 오늘은 문자열에서 특정 문자를 추출하는 함수나 위치를 추출하는 함수에 대해 알아보았는데요! 특히 위치를 찾는 함수는 여러 함수들과 중첩되어 사용하니
꼭 의미를 알아두는게 좋겠네요!
다음 시간에는 엑셀 함수의 꽃이라고도 불리는 VLOOKUP 함수를 다루어보도록 하겠습니다!
