1. 문제
A열은 단어 사이가 한 칸 공백(1바이트)로 되어 있는데, B열은 단어마다 공백을 포함해서 8바이트 길이로 만들려고 하는 것입니다. 그런데, 이 때 영어, 숫자와 기호는 1바이트, 한글은 2바이트로 계산하려고 합니다.
2. 해법
가. TextSplit와 Concat 함수 사용
TextSplit가 지원되는 엑셀이라면 간단하게 수식을 만들 수 있습니다. 공백을 기준으로 문장에서 단어를 분리해내는 것을 ‘나’처럼 find, mid 등 함수를 이용하면 너무 복잡합니다.
(1) TextSplit 함수로 단어 나누기
=TEXTSPLIT(A2,” “)
라고 입력하면 B열부터 D열까지 분리된 단어가 표시됩니다.
(2) 단어를 공백을 추가해서 8바이트로 만들기
=MIDB(TEXTSPLIT(A2,” “)&REPT(” “,8),1,8)
라고 입력하는데, 이 수식의 의미는 분리된 단어에 rept(반복) 함수로 공백 8개를 추가한 다음 첫번째 위치부터 8바이트씩을 가져오는 것입니다.
MID가 아니라 MIDB인 것을 주의해야 합니다. 왜냐하면 MID함수는 한글도 1로 계산하기때문입니다.
수식을 마우스로 끌어서 선택하면 윗 부분에 결과가 보이는데, ‘선경 ‘,’1차’,’아파트” 다음에 공백이 추가된 것을 알 수 있습니다.
그런데 공백의 개수를 셀 수 없으므로 F9키를 누릅니다.
그러면 수식 입력줄에 결과가 표시되는데, 커서를 넣은 다음 ‘선경’ 다음의 공백을 세보면 4개인 것을 알 수 있고, ‘1차’ 다음의 공백은 5개인 것을 알 수 있고, 아파트 다음은 공백 2칸인 것것을 확인할 수 있습니다.
Esc키를 눌러 원래 수식으로 돌려 놓습니다.
(3) Concat 함수로 단어 합치기
=CONCAT(MIDB(TEXTSPLIT(A2,” “)&REPT(” “,8),1,8))
라고 기존 수식을 Concat 함수로 감싸면 아래와 같이 “선경 1차 아파트 “라고 하나로 합쳐져서 B2셀에 입력됩니다.
F9키를 눌러 수식의 결과에서 공백의 개수를 세어보면 동일한 것을 알 수 있습니다.
(4) 수식 복사하기
B2셀의 수식을 복사해서 B3셀에 붙여 넣거나 B2셀의 채우기 핸들을 더블 클릭하면 B3셀까지 수식이 복사됩니다.
나. Mid, Find, Substitute, Concat 함수 사용
이건 고난의 역사입니다.
(1) ‘선경’ 등 단어 분리하기
(가) ‘선경’ 분리하기
① Find 함수를 이용하는 방법
여러 가지 방법이 있지만, 여기서는 Find 함수를 이용하는 것이 이해가 쉬운 듯 합니다.
=LEFT(A2,FIND(” “,A2)-1)라고 입력하면
A2셀에서 첫번째 공백의 위치에서 1을 뺀 위치까지 글자를 가져오므로 ‘선경’이 됩니다.
② Substitute 함수를 이용하는 방법
=TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,50)),1,50))
위 수식의 의미는 공백을 모두 공백 50개로 만든 다음 Mid함수를 이용해 첫번째부터 50개를 가져온 다음, 좌우 공백을 제거하는 것입니다.
이것의 효용은 두번째부터 단어를 가져올 때 있습니다.
두번째 단어인 ‘1차’를 가져올 때는 1,50에서 1을 51로만 바꾸면 되고, 세번째 단어인 아파트를 가져올 때는 101,50으로 수정하면 간단히 구할 수 있습니다.
D3셀의 수식은
=TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,50)),51,50))로 위 수식과 51만 바뀌었습니다.
E3셀의 수식도
=TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,50)),101,50))로 51이 101로만 바뀌었습니다.
(나) ‘1차’ 분리하기
‘1차’는 첫번째 공백 다음부터 두번째 공백 전까지의 글자입니다.
따라서, 수식은
=MID(A2,FIND(” “,A2)+1,FIND(” “,A2,FIND(” “,A2)+1)-FIND(” “,A2)-1)로
FIND(” “,A2)+1는 첫번째 공백 위치 다음이 되고, 여기서는 3입니다.
FIND(” “,A2,FIND(” “,A2)+1)-FIND(” “,A2)-1은
두번째 공백의 위치를 FIND(” “,A2,FIND(” “,A2)+1)로 찾는데, FIND(” “,A2)+1)로 첫번째 공백 위치 다음부터 다시 공백의 위치를 찾으므로 두번째 공백의 위치인 6이 구해지고,
FIND(” “,A2)로 첫번째 공백의 위치를 빼는데, 그러면 6-3은 3이 되므로 2가 되도록 -1을 한 것입니다.
보기만 해도 복잡하죠?
(다) ‘아파트’ 분리하기
아파트는 두번째 공백 위치 다음부터 마지막까지이므로 오히려 간단합니다.
=MID(A2,FIND(” “,A2,FIND(” “,A2)+1)+1,10)
위 수식에서 FIND(” “,A2,FIND(” “,A2)+1)는 두번째 공백의 위치를 구하는 것이고, +1을 추가해서 다음 위치부터 가져오도록 하는데, 10개를 가져옵니다. 그러나, 10개라 하더라도 가져올 단어가 3개뿐이 안되기때문에 확인해보면 공백 없이 ‘아파트’만 표시됩니다.
(2) ‘선경’ 등 단어 합치기
(가) 방법 1
마찬가지로 공백을 추가하기 위해 rept(” “,8)을 사용하고, MidB를 이용해 8개만 가져오고, 단어를 합치기 위해 Concat함수를 사용합니다.
수식은 Concat 다음에 C2셀의 수식을 붙여 넣는데, MidB와 rept(” “,50)을 추가해야 합니다.
=CONCAT(MIDB(LEFT(A2,FIND(” “,A2)-1)&REPT(” “,8),1,8))
위 수식은 ‘선경 ‘만 출력하는 수식입니다.
나머지까지 연결하면
=CONCAT(MIDB(LEFT(A2,FIND(” “,A2)-1)&REPT(” “,8),1,8)&MIDB(MID(A2,FIND(” “,A2)+1,FIND(” “,A2,FIND(” “,A2)+1)-FIND(” “,A2)-1)&REPT(” “,8),1,8)&MIDB(MID(A2,FIND(” “,A2,FIND(” “,A2)+1)+1,10)&REPT(” “,8),1,8))
가 됩니다.
F9키를 눌러서 확인하면 4, 5, 3개의 공백이 추가된 것을 확인할 수 있습니다
(나) 방법 2
이번에는 C3셀부터 E3셀까지의 수식을 이용해서 합쳐보겠습니다.
=CONCAT(MIDB(TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,50)),1,50))&REPT(” “,8),1,8)&MIDB(TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,50)),51,50))&REPT(” “,8),1,8)&MIDB(TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,50)),101,50))&REPT(” “,8),1,8))
‘선경’에 공백을 추가하고 8바이트만 가져오는 수식인
MIDB(TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,50)),1,50))&REPT(” “,8),1,8)
을 복사한 후 &와 붙여넣기를 하고 1만 51로 수정하면 되니 너무 편리합니다.
(3) 3행에 수식 붙여넣기
F2셀과 G2셀을 마우스로 끌어서 선택한 후 채우기 핸들을 더블 클릭하면 3행에 수식이 아래와 같이 붙여넣어지고, 결괏값도 맞는 것을 확인할 수 있습니다.
(4) MidB함수를 LeftB함수로 바꾸기
MidB(단어,1,8)은 LeftB(단어,8)로 바꿀 수 있습니다
예) =CONCAT(LEFTB(TEXTSPLIT(A2,” “)&REPT(” “,8),1,8))
어렵게 왔네요.
그러나, 한 단계씩 밟아오면 긴 수식이 완성되는 기쁨을 느낄 수 있습니다.