엑셀 콤마를 탭으로 바꾸기 (엑셀 텍스트 나누기 마법사로 텍스트를 여러 열로 나누기)

아래는 손으로 쓴 숫자들로 이루어진 MNIST (Modified National Institute of Standards and Technology database) 이미지 데이터의 하나로 숫자 9를 나타낸다. 이를 엑셀 시트에 붙여서 조건부 서식 > 색조 기능을 통해 대략 정말 숫자 9처럼 보이는지 확인해보자.

 

0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,61,159,194,255,205,60,0,0,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,73,198,242,253,253,254,253,238,58,0,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,62,225,253,253,253,253,254,253,253,208,8,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,207,253,253,253,253,162,154,98,208,253,74,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,193,253,253,221,102,5,0,0,34,253,223,18,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,235,253,253,196,0,0,0,3,184,253,206,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,235,253,253,236,178,143,110,189,253,253,139,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,235,253,253,253,253,253,254,253,253,253,112,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,224,253,253,253,253,253,254,253,253,253,43,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,107,253,253,253,253,253,254,253,253,253,146,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,84,188,212,188,188,129,95,159,254,236,30,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,11,0,0,0,0,152,253,253,117,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,141,240,253,235,29,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,73,244,253,253,193,16,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,0,0,70,247,253,253,253,60,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,0,73,241,254,253,253,212,10,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,40,242,253,254,253,253,84,0,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,113,253,253,254,209,30,3,0,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,106,253,253,246,73,0,0,0,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,15,225,253,49,0,0,0,0,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

 

하지만 이대로 엑셀에 붙어넣으면 아래와 같이 하나의 행이 하나의 셀을 차지하게 붙여진다. 

위의 결과는 우리가 원하는 모습이 아니다. 콤마를 기준으로 하나의 셀에 하나의 값이 들어가도록 해야한다. 그러면 콤마를 어떻게 탭(TAB)으로 대체할 수 있을까?

 

아래와 같이 데이터 > 텍스트 나누기 기능을 이용하면 된다. 콤마를 기준으로 나누고자하는 셀을 다음과 같이 선택한다.

1단계에서 구분 기호로 분리됨을 선택하고 다음을 누른다. 

어떤 구분 기호를 기준으로 나눌 것인지를 선택한다. 여기서는 쉼표를 선택하고 다음을 누른다. 데이터 미리 보기 창을 통해 결과를 미리 볼 수 있다. 

 

마지막으로 열데이터 서식에 [일반]을 선택하고 마침을 누른다.

다음과 같이 하나의 셀에 있던 텍스트가 콤마를 기준으로 구분되어 각각의 셀에 들어간 것을 확인할 수 있다. 

행과 열크기를 균등하게 조정하여 보기 좋게 만들어보자. 그리고 조건부 서식의 색조 기능을 이용해서 흑백이미지처럼 나타내보자.

조건부서식 > 색조 > 기타규칙 을 선택하여 최소값을 흰색, 최대값을 검정색을 선택하고 확인을 누르면 다음과 같은 회색조의 숫자 9를 확인할 수 있다.

 

www.excelforum.com/excel-general/584250-replace-commas-with-tabs.html

Replace commas with tabs