엑셀(Excel) CODE, CHAR 함수 이용 영문자 알파벳 자동 증가시키기

일반적으로 단일 숫자나 알파벳은 드래그를 이용해서 자동증가를 시킬 수 있습니다. 하지만 텍스트와 숫자, 알파벳이 포함된 문자열은 엑셀이 패턴을 인식할 수 없기 때문에 증가시킬 수 없습니다. 이것을 가능하게 하려면 여러 함수를 조합해야 합니다. 오늘 사용할 함수는 CODE, CHAR 과 LEFT, RIGHT 입니다. 4가지 함수를 이용해서 문자열에 속해 있는 알파벳만 자동으로 증가시켜 보겠습니다.

 

 

▼ 왼쪽에 한글와 알파벳이 들어가 있는 텍스트를 오른쪽 화면에 나와 있는 것처럼 한글을 제외한 알파벳만 자동으로 증가시키고자 합니다. 일반적은 자동 증가 기능으로는 안됩니다.

 

▼ 그림처럼 두 개의 셀을 선택해서 드래그 해 보세요. 자신이 원하는 결과값이 나타나지 않을 겁니다. 직접 A 에서 B 로 증가 시킨 두 개의 셀을 선택하고 드래그 하면 다음 셀에서 C 와 D 가 될 것 같지만 선택한 두 셀의 패턴을 반복하게 됩니다. 왜냐하면 “나무” 라는 한글이 들어가 있기 때문입니다. 

 

▼ 한글을 제외한 알파벳만 자동으로 증가시키고 싶다면 어떻게 해야 할까요? LEFT 와 RIGHT 함수를 이용해서 한글과 알파벳을 분리한 후 CODE 함수로 변환한 아스키 코드값에 1 을 더하는 것입니다. 이것을 함수로 짜서 한방에 적용해 보겠습니다. 먼저 LEFT 와 RIGHT 함수에 대한 설명을 하겠습니다. LEFT 와 RIGHT 로 문자열을 분리합니다. 

 

▼ 다음은 RIGHT 함수로 분리한 알파벳을 증가시켜 보겠습니다. 그러기 위해서는 알파벳에 해당하는 아스키코드값으로 변환해야 합니다. 왜냐하면 아스키 코드값이 숫자이기 때문입니다. A 를 지칭하는 숫자값으로 변환한 뒤 1 을 더하고 역변환하면 증가한 알파벳이 되는 것입니다. 알파벳을 아스키 코드값으로 변환하는 함수가 CODE() 이며, 다시 역변환하는 함수가 CHAR() 입니다. 아래 코드처럼 RIGHT() 함수로 잘라낸 “A” 를 아스키 코드값(숫자) 로 변환하기 위해 CODE() 함수를 사용했습니다. 그리고 “A” 아스키 코드값에 1 을 더하고 다시 CHAR() 을 이용해서 변환한 결과 “B” 를 얻을 수 있었습니다.

 

CHAR(CODE(RIGHT(C2))+1)

 

 

 

▼ 한글을 잘라낸 코드와 알파벳을 증가시키는 코드를 합쳐서 문자열을 만듭니다. 전체 코드는 다음과 같습니다.

 

=LEFT(B3, LEN(B3)-1) & CHAR(CODE(RIGHT(B3))+1)

 

 

▼ 이제 위의 코드를 이용해서 다른 문자와 결합되어 있는 알파벳을 증가시켜 한번에 빈칸을 채워보겠습니다. 먼저 영역 전체를 선택하고 리본 메뉴 홈 탭 > 편집 > 찾기 및 선택 > 이동 옵션 메뉴를 클릭합니다. 

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel CHAR 함수 이용해서 특수문자, 알파벳 입력하기 ▶ 엑셀 Excel CHAR 이용해서 여러 셀의 내용을 줄 바꿈으로 표현하기 ▶ 엑셀 Excel 공백을 제거, 특정 문자 삭제 SUBSTITUE 함수 사용 ▶ 엑셀 Excel 지정한 영역의 모든 셀에 특수 문자 넣기 ▶ 엑셀 Excel 데이터 유형 반환하는 함수 TYPE 사용법

 

 

 

 

▼ 이동옵션 팝업창이 뜨면 빈 셀을 체크하고 확인을 누릅니다. 

 

▼ 다음은 수식 입력창에 전체 코드를 넣습니다. 반드시 이동옵션 선택이후 커서를 다른 곳에 가져가면 안됩니다. 수식 입력창에 커서를 두고 수식을 넣어야 합니다.

 

▼ 수식 입력이 끝나면 Ctrl + Enter 단축키를 누릅니다. 그러면 한방에 증가한 알파벳 값을 볼 수 있을 겁니다. 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel CHAR 함수 이용해서 특수문자, 알파벳 입력하기 ▶ 엑셀 Excel CHAR 이용해서 여러 셀의 내용을 줄 바꿈으로 표현하기 ▶ 엑셀 Excel 공백을 제거, 특정 문자 삭제 SUBSTITUE 함수 사용 ▶ 엑셀 Excel 지정한 영역의 모든 셀에 특수 문자 넣기 ▶ 엑셀 Excel 데이터 유형 반환하는 함수 TYPE 사용법