문자(이하 '텍스트')와 숫자를 분리하는 방법은 꽤 오랫동안 찾아봤던 내용이다.
간단하게 내용을 정리해보려고 한다.
텍스트와 숫자를 분리하는 방법은 크게 엑셀의 내장 함수를 이용하는 방법과 VBA나 매크로를 통한 방법 두 가지로 분류할 수 있다. 이번 포스팅에서는 내장함수로만 구현하는 방법만 정리하였다.
우선 이 경우에도 상황이나 방법에 따라 다르다. 쉬운 방법부터 차근차근 정리했다.
텍스트의 길이가 고정인 경우
텍스트의 길이가 고정이고 우리가 인식할 수 있다면, 해당 텍스트 길이나 숫자 길이에 따라 분리할 수 있다.
여기서도 2가지 방법이 있다.
(1) 엑셀의 고유기능인 텍스트나누기를 사용하는 방법
리본 메뉴 > 데이터 > 텍스트 나누기
텍스트 마법사로 '구분 기호로 분리됨' 선택하고 마우스로 나누고자 하는 문자열에 구분선을 만들어준다.
마침을 누르면 이렇게 텍슽와 숫자가 나눠진 데이터를 볼 수 있다.
(2) 엑셀의 내장 함수를 이용하는 방법
LEFT, RIGHT, MID 같은 함수로 분리해주는 방법인데 위 예시처럼 텍스트 길이값이 2라면 LEFT(A2, 2) 와 같은 식으로 추출할 수 있고 반대로 숫자 데이터는 RIGHT(A2,4)로 추출할 수 있다.
가장 간단한 상황이면서 가장 쉬운 방법이라고 할 수 있다.
텍스트의 길이가 고정이 아닌 경우(가변적일 경우)
그런데 현실 세계에서는 이렇게 깔끔하게 떨어지는 케이스는 흔치 않다.
텍스트 길이가 가변적이라고 가정해보자.
텍스트나 숫자 데이터의 길이가 전부 제각각이다. 심지어 숫자가 없는 데이터도 있다.
그러나 모두 텍스트가 숫자보다 먼저 시작한다는 걸 알 수 있다.
A1 셀의 데이터를 B1에는 텍스트 데이터를, C1에는 숫자데이터를 입력하고 싶다면,
이러한 경우 다음과 같은 수식을 구성할 수 있다.
1. 텍스트만 추출하기
=LEFT(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),LEN(A1)+1))-1)
함수를 간단하게 설명하면, 처음 숫자가 나온 인덱스값을 찾아와 LEFT함수에서 문자의 인덱스만큼 잘라준다.
이때, 문자로만 이루어진 값이라면 문자 전체를 추출한다.
FIND 함수의 찾으려는 값인 첫 번째 인수는 중괄호를 묶어 숫자를 하나씩 입력해준다. 이렇게 하면 찾으려는 값을 하나씩 순환하면서 숫자가 나오는지를 탐색한다.
2. 숫자를 추출하기
1번의 과정을 거치면 B1열에는 텍스트 데이터만 남게 될텐데,
숫자데이터는 다시 어렵게 갈 필요없이 A1에서 B1의 중복값을 지우고 남기면 된다.
=SUBSTITUTE(A1,B1,"")
SUBSTITUTE 함수는 말그대로 어떤 값에서 특정 값을 새로운 값으로 대체해주는 함수다.
이렇게 하면, 아래와 같은 결과를 얻을 수 있다.
SUBSTITUTE 함수에 대해 자세한 설명은 아래를 참고하면 된다.
SUBSTITUTE(text, old_text, new_text, [instance_num])
- Text 필수. 대체할 텍스트가 포함된 셀
- Old_text 필수. Text 인수에서 바꾸려는 텍스트
- New_text 필수. Old_Text를 바꾸려는 텍스트
- Instance_num 선택. old_text에서 인스턴스를 지정할 수 있다.
네 번째 인수가 개념이 조금 안잡힐 수 있는데 다음 예시를 보면 좋을 것 같다.
"가1가2가3"이라는 데이터가 있을 때, Old_text를 "가"라고 지정하고 New_text를 "나"라고 지정한 뒤,
Instance_num을 1라고 지정하면 "나1가2가3", 2라고 지정하면 "가1나2가3"...이런 식으로 바꿀 수 있다.
참고 자료
SUBSTITUTE function - Microsoft Support
This article describes the formula syntax and usage of the SUBSTITUTE function in Microsoft Excel. Description Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you
support.microsoft.com
만약, 이보다 더 복잡한 문자, 숫자 분리라면 아래 포스팅을 참고해주세요!
2023.11.07 - [Excel] - 엑셀(Excel) - 문자(텍스트)와 숫자 분리하기(사용자함수)
엑셀(Excel) - 문자(텍스트)와 숫자 분리하기(사용자함수)
이번에는 VBA를 활용해 사용자 함수를 이용해 다양한 상황에 적용해보자. 마지막 숫자 그룹만 분리하기 먼저 문자열을 한 번 보자. 모두 상품명, 단위, 가격으로 이루어진 문자열이다. 여기서 가
jinooh.tistory.com
'Excel' 카테고리의 다른 글
| 엑셀(Excel) - 문자(텍스트)와 숫자 분리하기(사용자함수) (3) | 2023.11.07 |
|---|---|
| 엑셀(excel) - 위도/경도 좌표값으로 두 지점 간 거리 구하기 (2) | 2023.05.24 |
| 엑셀(excel) - 데이터 검색하여 값 불러오기(VLOOKUP 함수 활용) (0) | 2023.01.30 |
| 엑셀(excel) - 숫자 범위의 값으로 IF 함수 대체하기(VLOOKUP 함수 활용) (1) | 2023.01.30 |
| 엑셀(excel) - 조건 값에 맞는 여러 열의 값 불러오기(VLOOKUP 함수 활용) (2) | 2022.06.16 |
댓글