이 자습서에서는 Excel의 수식을 사용하여 구분 기호로 셀을 분할하는 방법을 보여줍니다. 구분 기호는 텍스트 문자열 내에서 데이터 청크를 구분하는 문자입니다. 이 기사에서는 Excel의 수식을 사용하여 구분 기호로 셀을 분할하는 다양한 방법을 보여줍니다.
세션을 시작하기 전에 오늘의 예제 워크북에 대해 알아보겠습니다.
예제의 기반은 학생 관련 데이터(이름 , ID , 과정 , 도시 ). 이 데이터를 사용하여 다양한 상황에서 작동하는 다양한 방법을 보여줍니다.
모든 방법의 예는 별도의 시트에 저장됩니다.
연습 워크북
아래 링크에서 통합 문서를 다운로드할 수 있습니다.
Excel에서 수식을 사용하여 구분 기호로 셀을 분할하는 8가지 방법
경우에 따라 Excel에서 셀을 분할해야 할 수도 있습니다. 누군가가 인터넷, 데이터베이스 또는 동료에서 정보를 복사할 때 이러한 문제가 발생할 수 있습니다. 전체 이름이 있고 이름과 성으로 구분하려는 경우 Excel에서 셀을 분할해야 하는 경우의 간단한 예입니다.
1. Excel 문자열 함수와 SEARCH 함수를 결합하여 대시/하이픈 구분 기호로 구분된 텍스트로 분할
구분 기호로 분할하기 위해 가장 먼저 해야 할 일은 구분 기호 자체를 찾는 것입니다. 구분 기호를 찾으면 구분 기호의 양쪽에서 쉽게 분할할 수 있습니다. 검색 기능을 사용하겠습니다. 구분 기호를 찾은 다음 LEFT를 사용하여 텍스트에서 값을 추출합니다. , 중간 , 또는 오른쪽 기능.
1.1. LEFT 및 SEARCH 기능 통합
의 시작하자. LEFT 이후 함수에는 텍스트와 문자 수라는 두 개의 매개변수가 있습니다. 텍스트 값을 알고 있는 대로 텍스트를 삽입합니다. 문자 수는 SEARCH를 사용합니다. 기능.
단계:
- 먼저 셀을 선택하고 해당 셀에 수식을 입력합니다.
=LEFT(B5, SEARCH("-",B5,1)-1) - 또한 Enter 키를 누릅니다. 키보드에서 키를 누릅니다.
- 채우기 핸들 드래그 아이콘을 아래로 눌러 범위에 수식을 복제합니다. 또는 자동완성 범위, 더블 클릭 플러스(+ ) 기호.
- 드디어 결과를 볼 수 있습니다.
🔎 공식은 어떻게 작동합니까?
예에서 구분 기호는 하이픈 '–입니다. '. 검색 함수는 하이픈 위치를 제공했을 것입니다. 이제 하이픈 자체가 필요하지 않으며 하이픈보다 먼저 추출해야 합니다.
1.2. MID 및 SEARCH 기능 병합
이제 중간 값에 대해 작성해 보겠습니다. 이를 위해 MID & 검색 기능. 아래 절차를 따르도록 합시다.
단계:
- 먼저 셀을 선택하고 다음 수식을 입력하세요.
=MID(B5, SEARCH("-",B5) + 1, SEARCH("-",B5,SEARCH("-",B5)+1) - SEARCH("-",B5) - 1) - Enter 키를 누릅니다. .
- 범위에 수식을 복사하려면 채우기 핸들 아래로 기호. 또는 두 번 클릭할 수 있습니다. 더하기(+ ) 자동완성에 서명 범위.
- 마지막으로 이제 중간 값이 모두 분리된 것을 볼 수 있습니다.
🔎 공식은 어떻게 작동합니까?
다른 문자열 내의 한 텍스트 문자열의 위치는 SEARCH 에 의해 반환됩니다. 기능. 하이픈 옆의 문자부터 시작합니다. 제공하는 문자 수를 기준으로 MID 지정한 위치에서 시작하여 텍스트 문자열에서 특정 수의 문자를 검색합니다.
1.3. RIGHT, LEN 및 SEARCH 복합 기능
이제 맨 마지막 셀을 분리하기 위해 RIGHT 조합을 사용합니다. , 렌 및 검색 기능. 수식의 조합을 사용하여 구분 기호로 셀을 분할하는 단계를 살펴보겠습니다.
단계:
- 먼저 셀을 선택하고 해당 셀에 다음 수식을 삽입합니다.
=RIGHT(B5,LEN(B5) - SEARCH("-", B5, SEARCH("-", B5) + 1)) - Enter 키를 누릅니다. 키보드의 키를 한 번 더 누르세요.
- 그런 다음 채우기 핸들 아이콘을 클릭하여 범위에 수식을 복사합니다. 또는 더블 클릭 플러스(+ ) 징후. 이것은 또한 공식을 복제합니다.
- 따라서 마지막 값은 구분 기호로 분할됩니다.
🔎 공식은 어떻게 작동합니까?
여기, LEN 함수는 마지막 하이픈의 위치를 뺀 문자열의 전체 길이를 반환합니다. 검색 함수는 하이픈 위치를 제공했을 것입니다. 그런 다음 차이는 마지막 하이픈 뒤의 문자 수이며 RIGHT 함수가 그것들을 추출합니다.
참고: 유사한 방식으로 다른 문자로 열을 분할할 수 있습니다. '– '를 필수 구분 기호와 함께 사용합니다.자세히 알아보기: 쉼표로 문자열을 분할하는 Excel 수식(예제 5개)
2. 줄 바꿈으로 텍스트를 분할하기 위한 수식 병합
줄 바꿈으로 문자열을 분할하려면 이전 섹션과 유사한 공식을 사용합니다. 이전 공식에 추가해야 하는 한 가지 추가 기능입니다. 함수는 CHAR입니다. .
2.1. LEFT, SEARCH 및 CHAR 기능 결합
이 CHAR 함수는 줄 바꿈 문자를 제공합니다. 첫 번째 값을 가져와 셀에서 분리하려면 LEFT를 사용합니다. , 검색 , 및 CHAR 기능. 이에 대한 절차를 살펴보겠습니다.
단계:
- 이전 방법과 마찬가지로 먼저 아무 셀이나 선택하고 다음 수식을 입력하여 최상위 값을 추출합니다.
=LEFT(B5, SEARCH(CHAR(10),B5,1)-1) - Enter 키를 누르십시오. 결과를 확인하는 열쇠입니다.
- 또한 더하기 기호를 드래그하여 수식을 복사하고 셀 범위에 대한 결과를 얻을 수 있습니다.
🔎 공식은 어떻게 작동합니까?
10 ASCII입니다. 라인에 대한 코드. 10 CHAR 내 줄 바꿈을 검색합니다. 숫자로 결정되는 문자가 반환됩니다. 또한 휴식을 검색합니다. 그 후 최상위 값을 반환합니다.
2.2. MID, SEARCH 및 CHAR 기능을 함께 추가
중간 값을 구분하기 위해 아래 단계를 살펴보겠습니다.
단계:
- 다른 접근 방식과 마찬가지로 먼저 아무 셀이나 선택하고 다음 공식을 입력하여 가장 높은 값을 추출합니다.
=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5, SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1) - 결과를 보려면 Enter 키를 누릅니다. 키.
- 또한 수식을 복제하고 더하기 서명.
2.3. RIGHT, LEN, CHAR 및 SEARCH 기능에 참여
이제 텍스트의 오른쪽에 대한 공식은 RIGHT , 렌 , CHAR 및 검색 기능. 나머지 값에 대해 적절한 공식을 사용하십시오. 따라서 하단 값을 분리하려면 지침을 따르십시오.
단계:
- 이전 기술과 마찬가지로 셀을 선택하고 다음 수식을 입력하여 최하위 값을 추출합니다.
=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) + 1)) - Enter 키를 누릅니다. 키보드에서 키를 누릅니다.
- 마지막으로 수식을 복제하고 더하기 기호를 드래그하여 지정된 셀 범위에 대한 답을 검색할 수 있습니다.
자세히 알아보기: Excel에서 셀을 분할하는 방법(5가지 쉬운 트릭)
3. Excel에서 텍스트 및 숫자 문자열 패턴으로 셀 분할
이 섹션에서는 문자열 뒤에 숫자가 오는 텍스트를 분할하는 방법을 살펴보겠습니다. 간단하게 하기 위해 시트에 몇 가지 변경 사항을 적용했습니다. 이 예에는 학생 이름이 있습니다. 및 ID 함께 열에 넣고 두 개의 다른 열로 나눕니다.
3.1. 결합 RIGHT, SUM, LEN 및 SUBSTITUTE 함수
SUBSTITUTE 내 숫자를 공백으로 바꾸고 LEN을 사용하여 계산합니다. . 숫자 형식 문자열이 뒤따르는 텍스트를 분할하려면 먼저 숫자를 찾은 다음 추출된 숫자의 도움으로 텍스트를 추출해야 합니다.
단계:
- 처음에 결과를 넣을 셀을 선택합니다. 우리의 경우 C5 셀을 선택합니다. .
- 그런 다음 해당 셀에 수식을 삽입합니다.
=RIGHT(B5,SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"},"")))) - Enter 키를 누르십시오. 키.
- 또한 수식을 복제하고 더하기 기호를 끌어 셀 범위에 대한 답을 검색할 수도 있습니다.
🔎 공식은 어떻게 작동합니까?
숫자를 추출하려면 0에서 가능한 모든 숫자를 찾아야 합니다. 9까지 우리의 문자열 내에서. 그런 다음 숫자 합계를 구하고 문자열 끝에서 문자 수를 반환합니다.
3.2. LEFT 및 LEN 기능 통합
텍스트 값을 추출하려면 이제 LEFT를 사용해야 합니다. 함수 및 문자 수에 대한 자리 표시자에서 해당 숫자의 셀 길이의 총 길이를 제공합니다. D5 셀에서 숫자를 가져옵니다. , ID를 분할할 때 이전 방법에서.
단계:
- 처음에는 특정 셀을 선택하고 거기에 수식을 입력합니다.
=LEFT(B5,LEN(B5)-LEN(D5)) - Enter 키를 누릅니다. .
- 더하기 기호를 드래그하여 수식을 복제하고 셀 그룹에 대한 결과를 얻을 수 있습니다.
자세히 알아보기: Excel VBA:문자 수로 문자열 분할(2가지 쉬운 방법)
4. 수식을 사용하여 숫자 및 텍스트 문자열 패턴으로 셀 나누기
' 분할 방법을 이해했다면 텍스트 + 숫자 ' 그런 다음 텍스트 형식이 뒤따르는 일련의 숫자를 분할하는 방법을 상상하기 시작하셨기를 바랍니다. 접근 방식은 이전과 동일하며 한 가지 변경 사항만 확인할 수 있습니다. 이제 숫자가 텍스트 왼쪽에 있으므로 LEFT를 사용해야 합니다. 숫자를 가져오는 함수와 문자 텍스트의 경우 RIGHT 기능.
4.1. LEFT, SUM, LEN 및 SUBSTITUTE 함수 병합
최상위 값에 대한 숫자 및 텍스트 문자열 패턴으로 셀을 분할하려면 LEFT를 병합해야 합니다. , 합 , 렌, 및 대체 기능.
단계:
- 먼저, 처음에 특정 셀을 선택하고 거기에 수식을 입력합니다.
=LEFT(B5, SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"}, "")))) - Enter 키를 누르십시오. 키.
- 또한 더하기 기호를 드래그하여 수식을 복제하고 셀 그룹에 대한 결과를 얻을 수 있습니다.
4.2. RIGHT 및 LEN 복합 기능
오른쪽을 결합해야 합니다. 및 LEN 마지막 값에 대한 숫자 및 텍스트 문자열 패턴으로 셀을 분할하는 함수입니다.
단계:
- 시작하려면 특정 셀을 선택하고 거기에 수식을 입력하세요.
=RIGHT(B5,LEN(B5)-LEN(C5)) - Enter 키를 누릅니다. 버튼.
- 또한 수식을 복제하고 더하기 기호를 끌어 셀 집합에 대한 답을 얻을 수 있습니다.
5. RIGHT, LEN, FIND 및 SUBSTITUTE 함수를 결합하여 셀에서 날짜 분할
텍스트에서 날짜를 분할하려면 RIGHT 조합을 사용할 수 있습니다. , 렌 , 찾기 및 대체 기능.
단계:
- 원하는 셀을 선택한 다음 거기에 수식을 입력합니다.
=RIGHT(B5,LEN(B5)-FIND(" ",SUBSTITUTE(B5," "," ",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-2))) - 또한 Enter 키를 누릅니다. 키.
- 또한 수식을 복제하고 더하기 기호를 끌어 셀 집합에 대한 결과를 얻을 수도 있습니다.
🔎 공식은 어떻게 작동합니까?
날짜 값이 문자열 끝에 있으므로 월, 날짜 및 연도를 추상화할 수 있도록 여러 인스턴스를 탐색했습니다. 목표 값이 구동하는 데 더 많은 텍스트가 필요한 경우 인스턴스 수를 변경하여 추출할 수 있습니다.
참고: 이 수식은 텍스트 문자열 끝에 날짜가 있는 경우에만 유용합니다.자세히 알아보기: Excel VBA:문자열을 셀로 분할(4가지 유용한 응용 프로그램)
6. FILTERXML 및 SUBSTITUTE 함수를 결합하여 셀 분할
제공된 xpath를 사용하여 FILTERXML 함수 XML에서 특정 데이터 추출 서류. FILTERXML 및 대체 세포를 분리하는 기능. Excel에서 수식을 사용하여 구분자로 셀을 분할해 봅시다.
단계:
- 먼저 원하는 셀을 선택하고 거기에 수식을 입력합니다.
=FILTERXML("<t><s>"&SUBSTITUTE(B5,",","</s><s>")&"</s></t>","//s[2]") - 그런 다음 Enter 키를 누르십시오. 결과를 확인하세요.
- 마지막으로 더하기 기호를 드래그하여 수식을 복제하고 셀 모음에 대한 결과를 얻을 수 있습니다.
🔎 공식은 어떻게 작동합니까?
여기 SUBSTITUTE 텍스트 문자열의 특정 텍스트를 바꾸는 것입니다. 그런 다음 Excel의 FILTERXML 함수를 사용하면 XML에서 데이터를 가져올 수 있습니다. 파일.
7. 구분자로 셀 나누기에 TEXTSPLIT 함수 적용
TEXTSPLIT 함수를 사용합니다. 여기서 열과 행은 텍스트 시퀀스를 구분하는 구분 기호로 사용됩니다. 행 또는 열로 나눌 수 있습니다. 이것은 구분 기호로 모든 셀을 분할하는 가장 짧고 간단한 방법입니다. Excel에서 수식을 사용하여 구분자로 셀을 분할하려면 아래 단계를 따르십시오.
단계:
- 결과를 보고 싶은 셀을 선택하고 거기에 수식을 입력하세요.
=TEXTSPLIT(B5,",") - 그런 다음 Enter 키를 누릅니다. .
- 또한 수식을 복제하고 더하기 기호를 끌어 셀 집합에 대한 결과를 얻을 수 있습니다.
8. TRIM, MID, SUBSTITUTE, REPT 및 LEN 기능을 결합하여 셀 분할
수식의 또 다른 조합은 TRIM입니다. , 중간 , 대체 , REPT , 및 LEN 함수를 사용하면 Excel의 수식을 사용하여 구분 기호로 셀을 분할할 수 있습니다.
단계:
- 결과를 보고 싶은 셀에 수식을 선택 후 입력하세요.
=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5))) - 그런 다음 Enter 키를 누릅니다. .
- 더하기 기호를 밀어서 수식을 복제하고 셀 그룹에 대한 결과를 얻을 수도 있습니다.
🔎 공식은 어떻게 작동합니까?
여기, 렌 문자열의 길이를 문자로 반환합니다. 그런 다음 SUBSTITUTE 함수는 텍스트 문자열의 특정 지점에 나타나는 텍스트를 대체합니다. 그 후 MID 함수는 지정한 위치에서 시작하여 텍스트 문자열에서 특정 수의 단어를 제공합니다. 마지막으로 TRIM 이 함수는 단어 뒤의 이중 공백을 제외하고 텍스트에서 모든 공백을 제거합니다.
Excel에서 명령을 사용하여 구분자로 셀을 분할하는 방법
Excel에는 셀 분할 기능이 있습니다. . 데이터 탭의 옵션에서 찾을 수 있습니다. . Excel에서 수식을 사용하여 구분자로 셀을 분할하는 기능을 사용하려면 아래 지침을 따라야 합니다.
단계:
- 먼저 셀이나 열을 선택합니다(전체 열을 선택해야 하는 경우가 더 많음).
- 그런 다음 데이터 탐색 탭 . 여기 데이터 도구 내 섹션에서 텍스트를 열로 .
- 그 후 클릭하세요.
- 대화 상자가 눈앞에 나타납니다. 일반적으로 구분자로 셀을 분할해야 하므로 Delimited 옵션을 선택하고 다음을 클릭합니다. .
- 그러면 여러 구분 기호가 포함된 인터페이스를 찾을 수 있습니다.
- 또한 원하는 것을 선택하거나 고유한 구분자를 입력할 수도 있습니다.
- 구분 기호를 선택하면 상자 하단에 결과가 표시됩니다.
- 또한 다음을 클릭합니다. 그럼.
- 이 예에서는 값이 쉼표로 구분되었으므로 여기에서 쉼표를 선택했습니다.
- 다음을 클릭한 후 값 유형을 선택하고 마침을 클릭하는 옵션이 있습니다. . 별도의 값을 받게 됩니다.
- 당분간 일반으로 유지합니다. (기본값) . 아래 이미지의 포맷은 약간의 포메이션을 거쳐 제작된 것입니다.
결론
위의 방법은 Excel의 수식을 사용하여 구분 기호로 셀을 분할하는 데 도움이 됩니다. 오늘은 그게 다야. 위의 방법으로 Excel의 수식을 사용하여 구분 기호로 셀을 분할할 수 있습니다. 수식을 사용하여 구분 기호로 셀을 분할하는 몇 가지 방법을 나열해 보았습니다. 이것이 도움이 되기를 바랍니다. 이해하기 어려운 점이 있으면 언제든지 의견을 말하십시오. 여기에서 놓친 경우 다른 방법으로 알려줄 수도 있습니다.
추가 자료
- Excel에서 하나의 셀을 두 개로 나누는 방법(5가지 유용한 방법)
- Excel에서 단일 셀을 반으로 나누는 방법(대각선 및 수평)
- Excel에서 문자열을 여러 열로 분할하는 VBA(2가지 방법)
- Excel VBA:문자열을 문자로 분할(6가지 유용한 예)
- Excel에서 한 셀에 두 줄을 만드는 방법(4가지 방법)