Excel에서 덮어쓰지 않고 텍스트를 열로 변환하는 방법

일상적인 작업에서 Excel의 텍스트를 열로 변환해야 하는 경우가 많습니다. 사실, Microsoft Excel은 이 작업을 수행하는 훌륭한 도구입니다. 그리고 분명히 이 주제에 대한 적절한 수의 기사를 찾을 수 있습니다. 그러나 이 기사의 전문은 여기에서 다양한 방법으로 Excel에서 덮어쓰지 않고 텍스트를 열로 변환하는 방법을 보여줍니다. 즉, 원본 열은 덮어쓰지 않고 그대로 유지됩니다. 그러니 혼자서 효율적으로 작업을 해보세요.

더 나은 이해와 연습을 위해 다음 Excel 워크북을 다운로드할 수 있습니다.

Excel에서 덮어쓰지 않고 텍스트를 열로 변환하는 5가지 방법

이해를 돕기 위해 학생의 이름 및 나이 목록을 사용할 것입니다. 특정 기관의. 이 데이터세트에는 이름, 나이가 포함됩니다. B열 .

이제 이 텍스트를 B열로 변환하겠습니다. 여러 가지 방법으로 덮어쓰지 않고 다른 열에. 여기서 덮어쓰기란 ​​작업을 수행하는 동안 본문이 손상되지 않음을 의미합니다. 그럼 하나씩 알아보도록 하겠습니다.
여기에서는 Microsoft Excel 365를 사용했습니다. 버전, 귀하의 편의에 따라 다른 버전을 사용할 수 있습니다.

1. LEN, MID, REPT, SUBSTITUTE 및 TRIM 기능 적용

공식을 가지고 노는 것을 좋아하는 사람들에게 이 방법은 적합합니다. 이 방법에서는 몇 가지 함수를 결합하여 공식이 작동하도록 할 것입니다. 자, 실행을 봅시다.

📌 단계:

• 처음에 2를 만듭니다. 제목이 이름인 다른 열 및 나이 C 열 아래 및 D .
• 셀 C5 및 D5 , 1을 기록 및 2 각기. 이것은 이러한 새 열의 수입니다. 다음 단계에서 이 두 세포의 기능을 이해하게 될 것입니다.

• 그런 다음 C6 셀을 선택합니다. 다음 공식을 입력하세요.

여기, B6 및 C5 셀은 첫 번째 이름, 나이를 나타냅니다. 및 숫자 1 .

• LEN($B6) → LEN 함수 텍스트 문자열의 문자 수를 반환합니다. 여기 B6 셀은 텍스트입니다. 값 11을 산출하는 인수.
  • 출력 → 11
• REPT(” “,LEN($B6)) → 된다
  • REPT(" ",11) → REPT 기능 주어진 횟수만큼 텍스트를 반복합니다. 여기서 " "는 텍스트입니다. 공백을 참조하는 인수 11 동안 공백 number_times입니다. 11을 삽입하도록 함수에 지시하는 인수 공백을 반복해서 사용합니다.
  • 출력 → "           "
• 대체($B6," ",REPT(" ",LEN($B6))) → SUBSTITUTE 기능 기존 텍스트를 텍스트 문자열의 새 텍스트로 바꿉니다. 여기 B6 텍스트를 나타냅니다. 인수 동안 다음, “,” old_text를 나타냅니다. 인수 및 REPT(“ ”,LEN($B6)) new_text를 가리킵니다. 쉼표를 공백으로 바꾸는 인수.
  • 출력 → John           18세
• MID(대체($B6,",",REPT("",LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6) )  → MID 기능 시작 위치와 길이가 주어지면 텍스트 문자열의 중간에서 문자를 반환합니다. 여기서 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))) → 된다
  • 트림(존         ) → 트림 기능 텍스트에서 단일 공백을 제외한 모든 것을 제거합니다. 여기, 존           셀은 텍스트입니다. 인수, 그리고 함수는 텍스트 뒤의 초과 공백을 제거합니다.
  • 출력 → 존

• 그런 다음 Enter 키를 누릅니다. .

• 둘째, 채우기 핸들을 드래그합니다. 행 전체에 수식을 복사하는 도구입니다.

따라서 D6 셀에서 나이인 텍스트의 오른쪽 부분을 볼 수 있습니다. .

• 이때 C6 두 개의 셀을 선택합니다. 및 D6 함께. D6 셀의 오른쪽 하단에 커서를 놓습니다. . 채우기 핸들을 볼 수 있습니다. 도구를 다시 사용하세요.
• 그런 다음 더블 클릭하여 아래 셀에 수식을 적용합니다.

다음은 우리 앞에 나타나는 결과입니다.

자세히 알아보기: Excel에서 수식을 사용하여 텍스트를 열로 자동 분할하는 방법

2. LEFT, LEN, RIGHT 및 SEARCH 기능 삽입

복잡한 표현식을 작성하고 기능을 결합하는 것이 걱정되십니까? 그렇다면 우리의 두 번째 방법은 당신의 기도에 대한 응답입니다! 이 방법에서도 수식을 사용하지만 이번에는 이해하기 쉬울 것입니다. 그러니 더 지체하지 말고 어떻게 하는지 봅시다.

📌 단계:

• 우선 C5 셀로 이동합니다. 다음 공식을 삽입하세요.

여기, B5 첫 번째 이름, 나이 역할 .

• 검색(“,”,B5) → 검색 기능 다른 텍스트 문자열 내에서 한 텍스트 문자열의 시작 위치를 반환합니다. 여기, “,” find_text입니다. 인수 동안 B5 in_text 논쟁. 특히 검색 기능 쉼표(,)의 위치를 ​​반환합니다. 텍스트 문자열의 문자.
  • 출력 → 5

• LEFT(B5,SEARCH(“;”,B5)-1) → 됩니다
  • 왼쪽(B5,5) → LEFT 함수 문자열의 시작 부분부터 지정된 수의 문자를 반환합니다. 여기 B5 셀은 텍스트입니다. 인수인 반면 5 num_chars입니다. 함수가 5를 반환하도록 하는 인수 왼쪽부터 문자.
  •  출력 → 존

• 둘째, ENTER를 탭합니다. 키.

이제 다른 유사한 수식을 사용하여 D5 셀에서 결과를 찾습니다. . 이전 공식에서 , LEFT 함수를 사용했습니다. 텍스트 문자열의 첫 번째 부분을 가져옵니다. 여기에서 문자열의 두 번째 부분을 가져옵니다.

• 현재 D5 셀로 이동 다음 공식을 삽입하세요.

• LEN(B5)-검색(“,”,B5) → LEN 기능 B5의 문자열 길이를 반환합니다. 셀과 대조적으로 검색 기능 쉼표 (,)의 위치를 ​​반환합니다. 캐릭터.
  • 출력 → 11 – 5 → 6
• 오른쪽(B5,LEN(B5)-검색(“,”,B5)) → 됩니다
  • 오른쪽(B5,6) → 오른쪽 기능 문자열 끝에서 지정된 수의 문자를 반환합니다. 여기 B5 cell은 텍스트 인수인 반면 6 num_chars입니다. 함수가 6을 반환하도록 하는 인수 오른쪽에서 문자.
•  출력 → 18세

3. 텍스트 열 기능 사용

이 방법에서는 Excel의 텍스트를 열로 작업을 수행합니다. 덮어쓰지 않고 텍스트를 열로 변환하는 편리한 도구입니다. 따라서 아래 표시된 단계에 따라 절차를 관찰하고 학습해 보겠습니다.

📌 단계:

• 처음에는 B5:B14 범위.
• 그런 다음 데이터 탭.
• 그런 다음 데이터 도구 드롭다운 아이콘.
• 드롭다운 목록에서 텍스트를 열로 기능.

즉시 텍스트를 열로 변환이 열립니다. 대화 상자.

• 1단계에서 구분을 선택합니다. 데이터를 가장 잘 설명하는 파일 형식 선택 아래에서 섹션.
• 그런 다음 다음 버튼.

• 2단계에서 쉼표를 선택합니다. 구분자 섹션.
• 그런 다음 다음을 클릭합니다. .

• 세 번째이자 마지막 단계에서 목적지를 설정합니다. 셀을 C5로 .
• 마지막으로 마침을 클릭합니다. 버튼.

단계를 완료하면 Excel에 MsgBox가 표시됩니다. 경고와 함께. 걱정하지 마세요.

• 확인을 클릭하기만 하면 됩니다. 여기.

놀랍게도 텍스트를 열에 덮어쓰지 않고 Excel에서 수행합니다.

자세히 알아보기: Excel에서 캐리지 리턴과 함께 텍스트에서 열로 기능을 사용하는 방법

4. 플래시 채우기 기능 구현

복잡한 수식을 사용하는 것이 적합하지 않은 경우 다음 방법이 원하는 답이 될 수 있습니다. 여기에서는 플래시 채우기를 적용합니다. 덮어쓰지 않고 텍스트를 열로 변환하는 Excel의 기능입니다.

📌 단계:

• 처음에는 John이라고 적습니다. C5 셀 . 이 셀에서 원하는 결과입니다. 하지만 처음으로 수동으로 해야 합니다.
• 그런 다음 홈으로 이동합니다. 탭.
• 그런 다음 채우기를 클릭합니다. 수정의 드롭다운 아이콘 그룹.
• 다음으로 플래시 채우기를 선택합니다. 기능.

마술처럼 Excel은 나머지 셀을 자동으로 채웁니다.

마찬가지로 , 나이에 대해 동일한 작업을 수행합니다. D열 아래의 열 .

자세히 알아보기: Excel 텍스트에서 열로의 구분 기호로 줄 바꿈을 사용하는 방법

5. VBA 코드 적용

수식을 사용하면 데이터를 빠르게 편집할 수 있지만 해석하기 어려울 수 있습니다. 또한 자주 자동으로 수행해야 하는 경우 VBA를 고려할 수 있습니다. 아래 코드.

📌 단계:

• 주로 개발자 탭.
• 두 번째로 Visual Basic을 선택합니다. 코드 그룹.
• 또는 키보드 단축키 ALT + F11을 사용합니다. 작업을 복제합니다.

갑자기 Microsoft Visual Basic for Applications 창이 나타납니다.

• 여기에서 Sheet6(VBA)을 두 번 클릭합니다. 프로젝트 탐색기 섹션에서.

즉시 오른쪽에 코드 모듈이 열립니다.

• 이 경우 다음 코드를 복사하여 모듈에 붙여넣습니다.

Sub Text_to_Columns_without_Overwriting()
Dim Arr() As String, cnt As Long, j As Variant
For k = 5 To 14
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로 나뉩니다. 단계.

• 첫 번째 부분에서 서브루틴에 이름이 지정됩니다. 여기서는 Text_to_Columns_without_Overwriting()입니다. .
• 다음으로 Arr 변수를 정의합니다. , 중 , 및 j 문자열로 , 긴 , 및 변이 .
• 두 번째 물약에서는 For 루프를 사용합니다. 각 셀을 통해 쉼표로 구분된 텍스트를 분할합니다.
• 이제 코드에서 For k =5 To 14 데이터의 시작 및 끝 행 번호를 나타냅니다. 여기서는 5입니다. 14까지 .
• 그러면 “,” Arr =Split(Cells(k, 2), ";") 원하는 경우 세미콜론, 파이프 등으로 변경할 수 있는 구분 기호입니다.
• 마지막으로 cnt =3 세 번째 열 번호를 나타냅니다(C열 ).

• 이제 리본의 녹색 재생 버튼을 클릭합니다. 사실은 달리기 단추. 또한 F5 키를 누를 수도 있습니다. 같은 작업을 수행할 수 있습니다.

궁극적으로 결과는 아래 주어진 스크린샷에 나타나야 합니다.

연습 섹션

스스로 연습할 수 있도록 연습을 제공했습니다. 오른쪽에 있는 각 시트의 아래와 같은 섹션. 직접 해보세요.

결론

이 문서에서는 Excel에서 덮어쓰지 않고 텍스트를 열로 변환하는 방법을 간단하고 간결하게 설명합니다. 연습을 다운로드하는 것을 잊지 마세요. 파일. 이 기사를 읽어 주셔서 감사합니다. 도움이 되었기를 바랍니다. 질문이나 제안 사항이 있으면 의견 섹션에 알려주십시오. Exceldemy 웹사이트를 방문하세요. , 원스톱 Excel 솔루션 제공업체에서 자세히 알아보세요.

관련 기사

• 날짜에 대해 Excel에서 텍스트를 열로 사용하는 방법(간단한 단계 포함)
• [수정됨!] Excel 텍스트에서 열로 데이터가 삭제됨
• Excel에서 여러 구분 기호가 있는 열로 텍스트를 변환하는 방법