텍스트를 열로 변환 실제로 우리의 일상 생활에서 일반적인 작업입니다. 셀 수 없이 많은 상황에서 텍스트를 열로 분할해야 하는 경우 Microsoft Excel이 탁월합니다.
이 기사에서는 Excel에서 여러 구분 기호가 있는 열로 텍스트를 변환하는 방법의 모든 핵심을 살펴보겠습니다. .
Excel에서 여러 구분 기호가 있는 열로 텍스트를 변환하는 6가지 방법
이때 베스트셀러 목록을 가정하여 B4:B13 에 표시된 데이터세트 책 이름, 저자 및 장르가 포함된 셀 열. 이 시나리오에서는 Book Name , 저자 , 및 장르 별도의 열로. 따라서 더 이상 지체하지 않고 각 방법을 개별적으로 살펴보겠습니다.
여기에서는 Microsoft Excel 365를 사용했습니다. 버전, 귀하의 편의에 따라 다른 버전을 사용할 수 있습니다.
방법 1 :텍스트를 열 기능으로 사용
무엇보다도 먼저 Excel의 텍스트를 열로 여러 구분 기호가 있는 열로 텍스트를 변환하는 편리한 도구입니다. 따라서 아래 표시된 단계에 따라 절차를 관찰하고 학습해 보겠습니다.
📌 단계 :
- 처음에 B5:B13을 선택합니다. 셀>> 데이터 로 이동 탭>> 텍스트를 열로 클릭 옵션.
즉시 텍스트를 열로 변환 마법사가 나타납니다.
- 그런 다음 구분됨을 선택합니다. 옵션>> 다음 누르기 버튼.
- 이때 쉼표 구분 기호>> 다음 누르기 버튼.
- 다음으로 목적지 를 입력합니다. 선호하는 셀, 여기 C5입니다. 셀>> 마침 클릭 버튼.
- 이제 경고가 나타날 수 있습니다. 이 경우 확인 버튼.
결과적으로 최종 결과는 아래의 스크린샷과 같아야 합니다.
자세히 알아보기: Excel에서 캐리지 리턴과 함께 텍스트 열 기능을 사용하는 방법
방법 2 :TRIM, MID, SUBSTITUTE, REPT, LEN 기능 활용
한 가지 예로, 함수는 Excel 스프레드시트의 핵심이며 여기에서 TRIM , 중간 , 대체 , REPT , 및 LEN 여러 구분 기호가 있는 텍스트를 열로 분리하는 기능. 여기에서 LEN 함수는 문자열의 길이를 반환하고 REPT 기능은 텍스트를 반복합니다. 다음으로 대체 MID 함수는 이전 텍스트를 새 텍스트로 대체합니다. 함수는 텍스트 내의 문자를 반환합니다. 마지막으로 TRIM 함수는 추가 공백을 제거합니다.
📌 단계 :
- 먼저 C6으로 이동합니다. 셀>> 아래 주어진 방정식을 입력하십시오.
=TRIM(MID(SUBSTITUTE($B6,",",REPT(" ",LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6)))
여기 B6 , 및 C5 셀은 도서 이름, 저자, 장르를 나타냅니다. 열 및 숫자 1 .
공식 분석:
- LEN($B6) → 텍스트 문자열의 문자 수를 반환합니다. 여기 B6 셀 텍스트 43 값을 산출하는 인수 .
- 출력 → 43" “
- REPT(” “,LEN($B6)) → 된다
- REPT(” “,43) → 주어진 횟수만큼 텍스트를 반복합니다. 여기서 " " 텍스트 공백을 참조하는 인수 43 동안 공백 43 을 삽입하도록 함수에 지시하는 number_times 인수입니다. 반복적으로 공백입니다.
- 출력 → ” “
- 대체($B6,",",REPT(" ",LEN($B6))) → 기존 텍스트를 텍스트 문자열의 새 텍스트로 바꿉니다. 여기 B6 텍스트를 나타냅니다. 인수 동안 다음, “,” old_text를 나타냅니다. 인수 및 REPT(" ",LEN($B6)) new_text를 가리킵니다. 쉼표를 공백으로 바꾸는 인수.
- 결과 → “불황 경제 Abhijit Banarjee
- MID(SUBSTITUTE($B6,",",REPT(" ",LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6) ) → 시작 위치와 길이가 주어지면 텍스트 문자열의 중간에서 문자를 반환합니다. 여기서 SUBSTITUTE($B6,",",REPT(" ",LEN($B6))) 셀은 텍스트입니다. 인수, (C$5-1)*LEN($B6)+1 start_num입니다. 인수 및 LEN($B6) num_chars 입니다. 함수가 왼쪽에서 첫 번째 문자를 반환하도록 하는 인수.
- 산출량 → "불황 경제학 "
- TRIM(MID(SUBSTITUTE($B6,",",REPT(" ",LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($ B6))) → 된다
- TRIM(“불황 경제 ") → 텍스트에서 단일 공백을 제외한 모든 것을 제거합니다. 여기에서 “불황 경제학 ” 셀은 텍스트입니다. 인수 및 함수는 텍스트 뒤의 초과 공백을 제거합니다.
- 산출량 → '불황'
- 둘째, 채우기 핸들 도구를 사용합니다. 행 전체에 수식을 복사합니다.
- 셋째, C6:E6 셀>> 채우기 핸들 드래그 아래 셀에 수식을 적용하는 도구입니다.
마지막으로 출력은 아래 그림과 같아야 합니다.
자세히 알아보기: Excel에서 수식을 사용하여 텍스트를 열로 자동 분할하는 방법
방법 3 :LEFT, RIGHT, MID, LEN, FIND 기능 결합
또는 LEFT 콤보를 사용할 수 있습니다. , 오른쪽 , 중간 , 렌 , 및 찾기 여러 구분 기호가 있는 텍스트를 다른 열로 분할하는 기능. 이 경우 찾기 함수는 주어진 배열 내에서 숫자를 검색하고 LEN 함수는 텍스트 문자열의 길이를 반환합니다. 차례로 MID 함수는 텍스트 문자열의 중간에서 문자를 반환하는 반면 LEFT 및 오른쪽 함수는 문자열의 왼쪽과 오른쪽 끝에서 각각 텍스트를 추출합니다.
고객 목록 이 있다고 가정해 보겠습니다. B4:B12 에 표시된 데이터세트 이름, 국가 및 도시가 포함된 셀 세미콜론으로 구분된 텍스트가 있는 열. 여기에서 이름, 국가 및 도시를 분할하고자 합니다. 여러 열로 나누어 그 과정을 자세히 살펴보겠습니다.
📌 단계 :
먼저 C5 셀>> 수식 입력줄에 다음 표현식 삽입 .
=LEFT(B5,FIND(";",B5)-1)
위 식에서 B5 셀은 이름, 국가 및 도시 를 나타냅니다. 열.
공식 분석:
- 찾기(“;”,B5) → 다른 텍스트 문자열 내에서 한 텍스트 문자열의 시작 위치를 반환합니다. 여기, ";" find_text 인수 동안 B5 in_text 논쟁. 특히 찾기 함수는 세미콜론의 위치를 반환합니다. (; ) 텍스트 문자열의 문자.
- 출력 → 7
- LEFT(B5,FIND(“;”,B5)-1) → 된다
- 왼쪽(B5,7) → 문자열의 시작 부분부터 지정된 수의 문자를 반환합니다. 여기 B5 셀은 텍스트입니다. 인수인 반면 7 num_chars 입니다. 함수가 7을 반환하도록 하는 인수 왼쪽부터 문자.
- 출력 → 트레버
- 그 후 D5로 점프 cell>> 다음 표현식을 입력하세요.
=MID(B5,FIND(";",B5)+1,FIND("@",B5)-FIND(";",B5)-1)
공식 설명:
- 찾기(“@”,B5)-찾기(“;”,B5)-1 → 여기에서 찾기 함수는 세미콜론의 위치를 반환합니다. (; ) 및 속도 (“@” ) 텍스트 문자열 내의 문자.
- 16 – 7 – 1 → 8
- 찾기(“;”,B5)+1 → 예를 들어 찾기 함수는 세미콜론을 찾습니다. (; ) 텍스트 문자열 내의 문자.
- 7 + 1 → 8
- MID(B5,FIND(“;”,B5)+1,FIND(“@”,B5)-FIND(“;”,B5)-1) → 된다
- MID(B5,8,8) → 여기, B5 셀은 텍스트입니다. 인수, 8 start_num 입니다. 인수 및 8 num_chars 입니다. 함수가 8을 반환하도록 하는 인수 처음 8 이후의 문자 캐릭터.
- 출력 → 아이슬란드
- 나중에 E5 세포.
=RIGHT(B5,LEN(B5)-FIND("@",B5))
이 공식의 작동 원리:
- LEN(B5)-FIND(“@”,B5) → 렌 함수는 B5의 문자열 길이를 반환합니다. 대조적으로 찾기 함수는 비율의 위치를 반환합니다. (“@” ) 문자.
- 26 – 16 → 10
- 오른쪽(B5,LEN(B5)-FIND(“@”,B5)) → 된다
- 오른쪽(B5,10) → 문자열 끝에서 지정된 수의 문자를 반환합니다. 여기 B5 셀은 텍스트입니다. 인수인 반면 10 num_chars 입니다. 함수가 10을 반환하도록 하는 인수 오른쪽에서 문자.
- 출력 → 레이캬비크
결국 결과는 아래 스크린샷과 같아야 합니다.
방법 4 :플래시 채우기 사용
복잡한 수식을 사용하는 것이 적합하지 않은 경우 다음 방법이 원하는 답이 될 수 있습니다. 여기에서는 플래시 채우기 기능을 적용합니다. Excel의 텍스트를 여러 구분 기호가 있는 열로 변환합니다.
📌 단계 :
- 처음에 이름 Trevor 를 수동으로 입력합니다. C5 셀>> 홈 탭에서 채우기 드롭다운>> 플래시 채우기 선택 옵션.
이제 Excel에서 나머지 셀을 자동으로 채웁니다.
마찬가지로 국가 및 도시 열과 최종 출력은 아래 주어진 이미지와 같아야 합니다.
방법 5 :파워 쿼리 사용
물론 파워 쿼리 데이터를 빠르고 효과적으로 구성하고 분석하는 기능에도 불구하고 Excel에서 간과되는 기능입니다. 다음 섹션에서는 몇 번의 클릭만으로 텍스트를 여러 구분 기호가 있는 열로 변환하는 방법에 대해 설명합니다. 그럼 실제로 실행해 보겠습니다.
📌 단계 :
- 먼저 B4로 이동합니다. 셀>> 키보드 단축키 CTRL + T 누르기 Excel 표 삽입>> 확인을 누릅니다. .
- 다음으로 데이터 탭>> 표/범위에서 클릭 옵션.
얼마 지나지 않아 파워 쿼리 편집기 나타납니다.
- 여기서 열 분할을 누릅니다. 드롭다운>> 구분자별 선택 옵션.
- 나중에 세미콜론을 선택합니다. 옵션>> 구분자의 각 발생에 체크 삽입 옵션>> 확인 누르기 버튼.
- 이에 따라 더블 클릭 열 머리글의 이름을 바꾸려면>> 닫기 및 로드를 누릅니다. 파워 쿼리 종료 옵션 창.
마지막으로 모든 단계를 완료하면 다음과 같은 결과가 나타납니다.
자세히 알아보기: Excel 텍스트에서 열로의 구분 기호로 줄 바꿈을 사용하는 방법
방법 6 :VBA 코드 적용
Excel에서 텍스트를 여러 구분 기호가 있는 열로 변환해야 하는 경우가 많으면 VBA 코드를 고려할 수 있습니다. 아래에. 간단하고 쉽습니다. 따라해보세요.
📌 단계 :
- 우선 개발자 로 이동합니다. 탭>> Visual Basic 클릭 버튼.
순식간에 Visual Basic Editor 새 창에서 열립니다.
- 다음으로 삽입으로 이동합니다. 탭>> 모듈 선택 .
이제 참조의 편의를 위해 여기에서 코드를 복사하여 아래와 같이 창에 붙여넣을 수 있습니다.
Sub Separate_Text_String()
Dim Arr() As String, _
cnt As Long, _
j As Variant
For k = 5 To 13
Arr = Split(Cells(k, 2), ";")
cnt = 3
For Each j In Arr
Cells(k, cnt) = j
cnt = cnt + 1
Next j
Next k
End Sub
⚡ 코드 분석:
여기에서는 VBA에 대해 설명하겠습니다. 텍스트를 여러 구분 기호가 있는 열로 변환하는 데 사용되는 코드입니다. 이 경우 코드는 2단계로 나뉩니다.
- 첫 번째 부분에서 서브루틴에 이름이 지정됩니다. 여기서는 Separate_Text_String()입니다. .
- 다음으로 변수 Arr, cnt, 를 정의합니다. 및 j 문자열로 , 긴 , 및 변이 .
- 두 번째 물약에서는 For 루프를 사용합니다. 각 셀을 통해 세미콜론으로 구분된 텍스트를 분할합니다.
- 이제 코드에서 "For k =5 To 13" 데이터의 시작 및 끝 행 번호를 나타냅니다. 여기서는 5입니다. 13까지 .
- 그러면 “;” "Arr =Split(Cells(k, 2), ";")" 원하는 경우 쉼표, 파이프 등으로 변경할 수 있는 구분 기호입니다.
- 마지막으로 "cnt =3" 세 번째 열 번호(C열)를 나타냅니다. .
- 이에 따라 실행 을 누르십시오. 버튼 또는 F5 키보드의 키를 누릅니다.
궁극적으로 결과는 아래 주어진 스크린샷에 나타나야 합니다.
연습 섹션
연습을 제공했습니다. 연습할 수 있도록 각 시트의 오른쪽에 섹션이 있습니다. 반드시 직접 하시기 바랍니다.
결론
요약하면 이 기사에서는 Excel에서 여러 구분 기호가 있는 열로 텍스트를 변환하는 6가지 효과적인 방법을 보여줍니다. 따라서 전체 기사를 주의 깊게 읽고 무료 통합 문서를 다운로드하여 연습하십시오. 이제 이 기사가 도움이 되었기를 바라며 추가 질문이나 권장 사항이 있으면 여기에서 언제든지 의견을 말하십시오. 마지막으로 ExcelDemy를 방문하세요. 이와 같은 더 많은 기사를 위해.
관련 기사
- 날짜에 대해 Excel에서 텍스트를 열로 사용하는 방법(간단한 단계 포함)
- [수정됨!] Excel 텍스트에서 열로 데이터가 삭제됨
- Excel에서 덮어쓰지 않고 텍스트를 열로 변환하는 방법