엑셀, 구글 스프레드시트 작업 중에는 여러가지 문자열(String) 데이터를 다루게 되는데, 숫자, 문자, 그리고 숫자 형식이 혼합된 셀에서 필요한 자료를 분리 추출 하는 방법에 대한 설명입니다. [ 예제 파일 첨부]
간단한 문자열 추출은 MID 함수 하나만 제대로 알고 있으면 되겠지만, 다양한 형태의 문자열을 다루게 되면 RIGHT, LEFT 함수, 그리고 FIND, SUBSTITUTE 같은 함수도 제대로 알고 있으면 좋아요. (엑셀, 구글 스프레드시트 함수의 내용은 같고, 예제는 엑셀 파일로 첨부하였습니다.)
▼엑셀 예제 파일 다운로드▼
LEFT("문자열", 왼쪽에서 선택할 문자수)
셀에 다음과 같이 입력하면 '무궁화'가 입력되는데 공백을 포함해서 카운트 합니다. =LEFT("무궁화 꽃이 피었습니다.", 3) '문자열이 A1에 있다면 =LEFT(A1, 3) '문자열의 왼쪽에서 3개를 선택 =LEFT("무궁화 꽃이 피었습니다.", 5) '이렇게 입력하면, '무궁화 꽃'이 입력됩니다. '문자열의 왼쪽에서 5개를 선택
MID("문자열", 시작번호, 선택할 문자수)
아래와 같이 입력하면 '무궁화 꽃이'가 입력되는데 공백을 포함해서 카운트 합니다. =MID("무궁화 꽃이 피었습니다.", 1, 6) '문자열이 A1에 있다면 =MID(A1, 1, 6) '문자열의 첫번째 ~ 6개를 선택 =MID("무궁화 꽃이 피었습니다.", 5, 2) '이렇게 입력하면, '꽃이'가 입력됩니다. '문자열의 다섯번째 ~ 2개를 선택
RIGHT("문자열", 오른쪽에서 선택할 문자수)
Right 함수를 사용해서 다음과 같이 입력하면 ' 피었습니다.'가 입력되는데 공백을 포함해서 카운트 합니다. =RIGHT("무궁화 꽃이 피었습니다.", 7) '문자열이 A1에 있다면 =LEFT(A1, 7) '문자열의 오른쪽에서 7개를 선택 =RIGHT("무궁화 꽃이 피었습니다.", 2) '이렇게 입력하면, '다.'이 입력됩니다. '문자열의 오른쪽에서 2개를 선택
활용, 문자열에서 숫자 문자 추출 하기
| 기초자료 |
| 티셔츠 95사이즈 (36매) |
| 귤 500g (15개) |
위와 같은 형식의 기초자료가 A2 행에서 부터 있다고 할 때 아래와 같이 품목, 수치, 수량으로 나눠보겠습니다.
| 기초자료 | 품목 | 수치 | 수량 | 수치(숫자) | 수량(숫자) |
| 티셔츠 95사이즈 (36매) | 티셔츠 | 95사이즈 | 36매 | 95 | 36 |
| 귤 500g (15개) | 귤 | 500g | 15개 | 500 | 15 |
▶ 맨 앞의 '티셔츠'라는 품목을 가져오기 위해서 FIND 함수로 첫번째 공백(" ")을 찾아주면, 4가 나옵니다. 이제 LEFT 함수로 첫번째 문자에서 공백이 포함되어 있는 4에서 - 1한 숫자만큼 문자를 가져오면, '티셔츠'가 입력됩니다.
=LEFT(A2,FIND(" ",A2)-1)
▶ 아래 함수를 사용하여, 두번째 공백 다음에 있는 문자열을 찾아주면, '95사이즈'가 입력됩니다.
=LEFT(RIGHT(A2,LEN(A2)-FIND(" ",A2)), FIND(" ",RIGHT(A2,LEN(A2)-FIND(" ",A2)))-1)
▶ 괄호 안의 글자를 가져오기 위해서 아래 함수를 사용하여, '(' 와 ')' 사이의 문자열을 찾습니다.
=MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)
▶ 아래 함수는 'C2'에 있는 숫자와 문자가 섞여 있는 문자열에서, 숫자만 찾아 줍니다.
=SUMPRODUCT(MID(0&C2, LARGE(INDEX(ISNUMBER(--MID(C2, ROW(INDIRECT("1:"&LEN(C2))), 1)) * ROW(INDIRECT("1:"&LEN(C2))), 0), ROW(INDIRECT("1:"&LEN(C2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(C2)))/10)
▶ 아래 함수는 'D2'에 있는 숫자와 문자가 섞여 있는 문자열에서, 숫자만 찾아 줍니다. 위와 같이 입력 되어 있는 상태에서는 'E2' 셀의 우측 하단에 커서를 두면 '+' 모양으로 바뀌고 클릭하여 'F2'행으로 클릭하면 완성됩니다.
=SUMPRODUCT(MID(0&D2, LARGE(INDEX(ISNUMBER(--MID(D2, ROW(INDIRECT("1:"&LEN(D2))), 1)) * ROW(INDIRECT("1:"&LEN(D2))), 0), ROW(INDIRECT("1:"&LEN(D2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(D2)))/10)
문자열에서 숫자 데이터만 추출
아래 함수는 'A2' 셀에 있는 문자열에서 문자를 모두 제거하고, 숫자만 찾아 줍니다.(관용적으로 사용되는 함수입니다.)
=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)[ 엑셀, 수식을 찾아 바꾸기 ]
입력 해야 할 셀을 'A2'에서 만약 'C3'로 바꿔야 한다면 일일이 바꿔주는 것 보다 찾기 및 바꾸기를 메뉴에서 수식을 찾아 바꿔줍니다..
입력 할 셀을 클릭하고, 컨트롤(Ctrl) + H 키를 눌러 내용을 입력하고, 범위를 시트, 검색을 행, 찾을 위치를 수식으로 지정해서 바꾸기를 한 번 클릭 합니다.
모두 바꾸기를 클릭하면 다른 셀들이 영향을 받으므로 주의해야 합니다.(물론 컨트롤(Ctrl) + Z을 눌러 취소 할 수 있습니다)
[ 구글 스프레드시트, 수식을 찾아 바꾸기 ]
역시 마찬가지로 입력 하려는 셀을 클릭하고, 컨트롤(Ctrl) + H 키를 눌러 잧기 / 바꾸기 할 내용을 입력하고, 범위를 특정 범위로 지정하면 클릭한 셀이 자동으로 설정되고, 수식 내 검색 체크박스를 클릭해서 이 경우에는 모두 바꾸기를 클릭하고, 완료해줍니다.
문자열에서 문자 데이터만 추출
아래 함수는 'A2' 셀에 있는 문자열에서 숫자를 모두 아무것도 없는 데이터 ("")로 바꿔주는 방법으로 문자 데이터만 추출합니다.(관용적으로 사용되는 함수입니다.)
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")잘 살펴보면 SUBSTITUTE 함수를 사용하여, 0~9까지의 숫자를 순차적으로 공백("")으로 바꿔준다는 것을 알 수 있습니다.
[ SUBSTITUTE 함수 사용법 ]
SUBSTITUTE("문자열", "찾을 문자열", "바꿀 문자열")
=SUBSTITUTE(A2, 0, "") 'A2' 셀에서 '0'을 찾아 공백("")으로 바꿔줍니다.
※ 이 외에도, 비슷한 것 같으면서도 다른 수많은 문자열을 다룰 텐데요, 한 번에 함수나 VBA, 스크립트로 필요한 자료를 얻어내면 좋겠지만, 복잡한 자료의 차근 차근 자료를 분석한 다음 차례대로 가공해서, 필요한 자료를 얻어내면 좋아요~!
'엑셀 구글스프레드시트' 카테고리의 다른 글
| 엑셀 구글 스프레드시트 시트 이름 가져오기(함수, 앱 스크립트로 현재 시트 이름, 전체 시트 이름 가져오기) (0) | 2021.06.26 |
|---|---|
| 엑셀 <-> 구글 스프레드시트 가져오기, 다운로드 (구글 스프레드시트 간 엑셀 파일 변환하기) (0) | 2021.06.26 |
| 엑셀 구글 스프레드시트 시간을 분으로, 분을 시간으로 변환, 경과 시간 알아내기(문자열 시간 변환) (0) | 2021.06.22 |
| 엑셀 구글 스프레드시트 한국 나이 & 만 나이 계산 수식, 함수(주민번호, 일반 텍스트 등의 자료에서 추출) (0) | 2021.06.22 |
| 엑셀 구글 스프레드시트 다른 파일의 데이터 & 다른 스프레드시트 데이터 참조 하기(파일 열지 않고 값 불러 오기) (0) | 2021.06.22 |
