엑셀(Excel) - 한글과 영어 분리하기(내장함수, VBA)

 

 

 

 

 

 

 

영문자의 위치가 일정한 경우

 

아래와 같은 데이터셋이 있다고 가정해보자.

수식어+영어과일이름

 

띄어쓰기, 대소문자, 문자길이가 각각 다르지만 모두 영어가 오른쪽에 위치하고 있음을 알 수 있다.

 

이 경우 가장 처음에 나타나는 영문자의 위치를 추출하여

LEFT 함수를 사용하면 영문자만 추출할 수 있다.

 

=IFERROR(LEFT(A1,MIN(IFERROR(SEARCH({"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"},LOWER(A1)),1000))-1),"")

 

함수의 원리는 영문 알파벳을 찾아 모두 소문자로 바꿔주고,

소문자 알파벳이 나오는 첫번째 글자의 위치를 찾아 LEFT함수로 추출하는 방식이다.

 

이 때, 영문이 없을 경우 

 

결과는 아래와 같다.

 

그리고 SUBSTITUE 함수를 이용해 영문자만 추출한다.

최종결과는 C열에 아래와 같은 수식으로 표기할 수 있다.

=SUBSTITUTE(A1,B1,"")

 

 

 

 

혹시 SUBSTITUTE 함수를 잘 모르겠다면, 아래 포스팅을 참고하면 된다!

2023.02.21 - [Excel] - 엑셀(Excel) - 문자(텍스트)와 숫자 분리하기(내장함수)

엑셀(Excel) - 문자(텍스트)와 숫자 분리하기(내장함수)

 

 

영문자의 위치가 일정하지 않은 경우

만약 아래와 같이 영문자가 뒤죽박죽인 상태라면 어떻게 해야 할까?

영문의 위치와 순서까지 모두 불규칙적인 형태이다.

 

 

애석하게도 이 경우는 사용자함수를 사용할 수 밖에 없다.

VBA를 활성화시켜서 사용자함수를 만들어준다.

 

사용자 함수 코드는 아래와 같다.

 

Function ExtractLetters(text As String) As String
    Dim i As Integer
    Dim letter As String
    Dim result As String
    For i = 1 To Len(text)
        letter = Mid(text, i, 1)
        If letter Like "[A-Za-z]" Then
            result = result & letter
        End If
    Next i
    ExtractLetters = result
End Function

 

해당 함수는 문자열에서 문자를 하나씩 탐색하고 영문자면 결과값에 붙여서 영문자열만 만드는 원리다.

 

사용자 함수를 만들고 수식으로 입력하면 간단하게 영문자만 추출할 수 있다.

물론, 이 방법은 첫 번째 경우에도 사용할 수 있는 방법이다.

 

=ExtractLetters(A1)

 

 

만약, 문자와 숫자 분리라면 아래 포스팅을 참고해주세요!

 

2023.02.21 - [Excel] - 엑셀(Excel) - 문자(텍스트)와 숫자 분리하기(내장함수)

엑셀(Excel) - 문자(텍스트)와 숫자 분리하기(내장함수)

 

2023.11.07 - [Excel] - 엑셀(Excel) - 문자(텍스트)와 숫자 분리하기(사용자함수)

엑셀(Excel) - 문자(텍스트)와 숫자 분리하기(사용자함수)

출처: https://jinooh.tistory.com/63 [글쎄요, 아직 잘 모릅니다.:티스토리]