엑셀(Excel) CODE, CHAR 함수 이용해서 영문자 알파벳 자동 증가시키는 방법

 

환경: Microsoft Excel 365

 

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

 

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

엑셀(Excel) CODE, CHAR 함수 이용해서 영문자 알파벳 자동 증가시키는 방법

 

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

엑셀(Excel) CODE, CHAR 함수 이용해서 영문자 알파벳 자동 증가시키는 방법

 

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

엑셀(Excel) CODE, CHAR 함수 이용해서 영문자 알파벳 자동 증가시키는 방법

 

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

 

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

엑셀(Excel) CODE, CHAR 함수 이용해서 영문자 알파벳 자동 증가시키는 방법

 

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

 

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

엑셀(Excel) CODE, CHAR 함수 이용해서 영문자 알파벳 자동 증가시키는 방법

 

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

엑셀(Excel) CODE, CHAR 함수 이용해서 영문자 알파벳 자동 증가시키는 방법

 


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

엑셀(Excel) CODE, CHAR 함수 이용해서 영문자 알파벳 자동 증가시키는 방법

 

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

엑셀(Excel) CODE, CHAR 함수 이용해서 영문자 알파벳 자동 증가시키는 방법

 

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

엑셀(Excel) CODE, CHAR 함수 이용해서 영문자 알파벳 자동 증가시키는 방법

Posted by 녹두장군

댓글을 달아 주세요