한글과 영문을 기준으로 숫자 분리하기

아래와 같이 텍스트에 '오더'와 'Order'가 있을 때 그다음에 나오는 숫자를 분리하는 다양한 해법에 대해 알아보고자 합니다.

두가지 문자를 기준으로 숫자 분리(질문).xlsx

0.01MB

 

1. 방법 1

 

가. '오더'를 기준으로 숫자 분리하기

간단하게 생각할 수 있는 방법은 오더 또는 Order를 찾아서 그 위치를 기준으로 값을 가져오는 것인데,

오더는 2글자이고, Order는 5글자이다 보니 조건을 달리해야 하며,

 

Mid함수를 이용해  숫자를 분리해서 가져오는 경우 반환값이 문자이기 때문에 숫자로 변환하기 위해서는 Value 함수를 이용해야 합니다.

 

따라서, =VALUE(MID(B4,FIND("오더",B4)+3,10))이 됩니다.

 

Find함수는 구문이 FIND(find_text, within_text, [start_num]) 이므로

find_text는 "오더"이며, within_text는 글자가 들어 있는 텍스트이므로 B4셀로 지정하고,

start_num은 옵션이므로 지정하지 않았습니다.

 

Mid함수는 구문이 MID(text, start_num, num_chars) 인데,

text는 b4셀로 지정하고, start_num은 find함수를 이용해 구한 오더의 위치가 6이므로 여기에 3을 더한 위치가 start_num이 됩니다.

그리고, num_chars는 가져올 문자수인데, 일정하지 않으므로 넉넉하게 10으로 지정했습니다.

 

나. 'Order'를 기준으로 숫자 분리하기

수식은 아래와 같습니다.

=VALUE(MID(B4,FIND("Order",B4)+6,10))

 

'가'와 다른 것은 같고, 오더와 Order가 글자수가 다르기 때문에 3이 6으로 바뀐 것만 다릅니다.

 

다. '오더'인지, 'Order'인지에 따른 처리

'Order'가 없을 때 =FIND("Order",B5) 라는 수식으로 'Order'을 찾으면 #VALUE! 에러가 발생합니다.

 

에러가 아닐 때만은 수식으로 표현하면 NOT(ISERR(수식))이 되며,

이것을 위의 경우에 적용하면

=IF(NOT(ISERR(FIND("오더",B4))),MID(B4,FIND("오더",B4)+3,10),MID(B4,FIND("Order",B4)+6,10))

이 됩니다.

 

위 수식은 '오더'를 찾아서 에러가 아니면, 다시 말해 '오더'가 있으면, '오더'의 위치를 기준으로 이후 숫자를 가져오고, 아니면 'Order'를 기준으로 숫자를 가져오는 것입니다.

 

이때 Order은 대소문자를 구분하므로 order이라고 입력하면 에러가 발생합니다.

위 수식까지 적용하면 숫자와 점이 왼쪽 정렬되므로 문자입니다. 

 

따라서, Value함수를 이용해 문자를 숫자로 바꿔야 합니다.

=VALUE(IF(NOT(ISERR(FIND("오더",B4))),MID(B4,FIND("오더",B4)+3,10),MID(B4,FIND("Order",B4)+6,10)))

 

2. 방법 2

오더와 Order로 데이터가 구분되어 있는데,

Substitute함수를 이용해 'Order'를 '오더'로 통일할 수 있습니다. '오더'인 경우는 당연히 Substitute함수를 적용해도 변함이 없습니다.

 

Substitute의 구문은 SUBSTITUTE(text, old_text, new_text, [instance_num]) 인데,

text는 바꿀 텍스트이니까 B4셀이 되고,

old_text는 'Order', new_text는 '오더'가 됩니다.

instance_num은 몇 번째인지를 정하는 것이지만 옵션이므로 지정하지 않아도 되고, 위의 경우는 1이므로 생략합니다.

 

따라서, 수식은 =SUBSTITUTE(B10,"Order","오더")

 

이제 오더와 Order에 따른 분기가 필요하지 않으므로

아래와 같이 오더로만 find 하면 되는데, Mid함수 다음의 text를 Substitute 함수를 적용한 텍스트로 지정해야 합니다.

=MID(SUBSTITUTE(B4,"Order","오더"),FIND("오더",SUBSTITUTE(B4,"Order","오더"))+3,10)

 

왜냐하면 Substitute 함수를 적용한 텍스트가 아닌 B10으로 지정하면 '기준 오더 111.'에서 찾지 않고, '기준 Order 111.'에서 찾기 때문에 결과가 'er 111.'로 달라집니다.

 

그리고, Value함수를 적용하면 아래와 같습니다.

=VALUE(MID(SUBSTITUTE(B4,"Order","오더"),FIND("오더",SUBSTITUTE(B4,"Order","오더"))+3,10))

 

3. 방법 3

TextAfter 함수를 사용하면 더 쉽게 원하는 값을 구할 수 있습니다.

 

TextAfter는 특정 구분자 이후의 텍스트를 가져오는 함수로서

구문은 =TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found]) 이며,

Microsoft 365에서만 적용 가능한 함수입니다.

 

text는 분리할 텍스트가 있는 B4셀이 되고,

delimeter는 구분자로 위의 경우 Order가 될 수도 있고, 공백이 될 수도 있습니다.

instance_num은 몇 번째인지를 지정하는 것인데, 음수이면 거꾸로 찾습니다.

match_mode는 대소문자를 구분하는지 여부로 기본값은 구분하는 것입니다.

match_end는 아래와 같이 설명되어 있는데 의미를 잘 모르겠습니다.

 

if_not_found는 구분자를 기준으로 한 결괏값이 없을 때의 값을 지정하는 것입니다. 기본값을 #N/A입니다.

 

가. 오더를 기준으로 하는 경우

=VALUE(TEXTAFTER(SUBSTITUTE(B4,"Order","오더"),"오더"))

 

나. 공백 한 칸을 기준으로 하는 경우

=VALUE(TEXTAFTER(B4," ",-1))

 

두가지 문자를 기준으로 숫자 분리(완성).xlsx

0.01MB