문장에서 숫자만 추출하기(1)

숫자 추출하기.xlsx

0.01MB

 
 

1. Find 함수를 이용해 맨 오른쪽 공백의 위치 찾기(실패)

마지막 공백의 위치를 찾은 다음, 그 위치의 오른쪽 첫번째부터 숫자를 가져오면 됩니다.
 
Find 함수는 구문이
FIND(find_text, within_text, [start_num]) 이므로
세번째 인수로 시작 위치를 숫자로 지정할 수 있습니다.
 
숫자의 길이에서 왼쪽으로 시작 위치를 지정하려고 하는데
10이면 두번째가 아니라 첫번째 공백 위치까지 갈 수 있으므로 5로 지정하겠습니다.
 
그러면 수식은
=find(" ",a2, len(a2)-5)
가 됩니다.
 
공백의 위치 6이 구해졌습니다.

 
숫자를 추출하는 것은 그 위치에 1을 더한 위치부터 가져오는데
Mid함수를 이용하면 됩니다.
 
Mid함수의 구문은 MID(text, start_num, num_chars) 인데, 세번째 인수인 num_chars로 넉넉하게 10을 지정하면
수식은 =MID(A2,FIND(" ",A2, LEN(A2)-5)+1,10) 가 됩니다.
 
그런데 구해진 값이 0.1로 문자이므로

 
value함수를 이용해 값으로 바꿔야 합니다.

 
B2셀의 채우기 핸들을 더블 클릭하면 B6셀까지 숫자가 채워지는데
B5셀에서 오류가 발생합니다.

 
문장의 길이에서 5를 빼니 첫번째 공백 위치의 값을 구해서 그렇습니다.
 
그러면 5를 4로 수정하겠습니다. 제대로 값이 구해졌습니다.
이제 B4셀의 수식을 B2셀부터 B5셀까지 붙여넣으면 됩니다.

 
그런데 이번에는 숫자의 길이가 길어서 소숫점에서 잘리는 바람에 공백을 찾을 수 없기 때문에 에러가 납니다.

 
공백의 위치를 찾는 것으로는 안되겠습니다.
 
 

2. Substitute 함수를 이용해 공백을 채운 다음 가져오기(성공)

숫자 앞에 공백이 있으므로 공백을 20개 정도로 바꾼 다음
오른쪽 10개 정도를 가져오면 안전하게 숫자를 가져올 수 있습니다.
 
문자열을 대체하는 함수는 Substitute이며,
구문은 SUBSTITUTE(text, old_text, new_text, [instance_num]) 인데,
네번째 인수인 instance_num(몇 번째 old_text인지 지정)은 옵션이므로 생략합니다.
 
수식은 =substitute(" ", rept(" ",20))이라고 지정하면 되는데,
여기서 rept함수의 구문은  REPT(text, number_times)로 text를 지정한 횟수만큼 반복해서 표시하는 것입니다. 다시 말해 위 수식은 공백을 20번 채우는 것입니다.
 
수식의 결과는 아래와 같이 0.1 앞에 공백이 20개 들어가서 숫자만 동떨어져 있습니다.

 
이제 숫자를 가져오는데 right함수를 이용해 오른쪽으로부터 넉넉하게 10개를 가져오면 되는데,
숫자 왼쪽에 공백이 있는 상태로 반환됩니다.

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

이것의 장점은 공백을 20개를 넣어서 숫자를 가져올 때 부담이 없다는 것입니다.
다시 말해 10개가 아니라 right함수에서 10을 20으로 바꿔도 됩니다.
20으로 바꾼 다음 채우기 핸들을 더블 클릭했는데, 문제없이 숫자를 가져왔습니다.