[엑셀] 자리수 다른 동호수 정렬 (문자에서 숫자 추출)

동호수나 파일명 등을 정렬하면 오름차순이나 내림차순 정렬이 되지 않는 경우가 있습니다.

엑셀에서 정렬의 첫번째 기준은 첫번째 문자나 숫자 입니다. 그다음 두번째 문자.....

문자가 특수문자 알파벳 등이 섞여 있는 경우에는 공백 - 특수문자 - 숫자 - 알파벳 - 한글 순입니다.

물론 셀 전체가 텍스트가 아닌 숫자인 경우는 숫자가 제일 앞에 나타납니다.

이렇게 첫번째, 두번째 순으로 정렬을 하니 101호가 1001호 뒤에 정렬됩니다.

이유는 101호가 숫자가 아닌 텍스트이기 때문이죠. 그래서 숫자로 바꾸어 숫자의 크기로 정렬을 하여야 합니다.

☞ 정렬방법

1. 텍스트에서 숫자 추출

2. 숫자를 기준으로 정렬

텍스트에서 숫자 추출

□ 방법1 : LEFT, LEN함수

수식 = VALUE(LEFT(C4,LEN(C4)-1))

☞ 수식설명

1. LEN 함수 - 1 : 문자의 개수에서 1을 뺌

2. LEFT 함수 : 문자에서 좌측에서 부터 (문자개수 - 1)만큼 추출(반환)

3. VALUE 함수 : 텍스트를 숫자로 바꿈

□ 방법2 : SUBSTITUTE 함수

수식 = VALUE(SUBSTITUTE(C4,"호","",1))

☞ 수식설명

1. SUBSTITUTE 함수 : 문자에서 "호"를 ""로 바꿈 (호를 지움)

2. VALUE 함수 : 텍스트를 숫자로 바꿈

※ 위에서 VALUE함수를 사용하는 이유는 LEFT 함수나 SUBSTITUTE 함수로 추출한 숫자도 속성은 텍스트입니다. 그래서 텍스트를 값으로 바꾸는 과정이 필요합니다.

정렬

방법1열 또는 방법2열을 기준으로 정렬

관련글 보기

전화번호 추출		문자 추출(텍스트 함수)

첨부파일 보기

동호수 정렬.xlsx