[아.. 엑셀좀 해놓을껄]엑셀에서 함수 없이 텍스트 추출해서 합치기

간단하고 빠르게 해결하는 딘도르입니다.

문득 칼퇴하고 싶으면 엑셀부터 배워라 라는 문구가 생각이 났습니다.

저 나름대로 엑셀에 대해서 괜찮게 알고 있고, 업무에 큰 불편함이 없을 정도로 사용하고 있다 생각했습니다.

최근에 거만하고 안일한 생각을 하고 있는 저를 정신이 번쩍 들게 하는 기능이 있었습니다.

여러분들에게 이 기능을 소개하면서 다시 한번 겸손이라는 단어를 되새겨야겠습니다.

이번 편에서는 규칙적인 데이터를 이용해서 필요한 텍스트를 만들어보는 방법에 대해 소개해보겠습니다.

---

엑셀에서 함수 없이 텍스트 추출해서 합치기

OS : Windows 10 Home

Program : Microsoft Office 365 Excel

타 버전에서도 동작할 수 있는 기능입니다. :)

많은 데이터를 받았을 때 우리의 입맛에 맞지 않게 구성이 되어 있는 경우가 종종 있습니다. 이 문자열은 앞에 있었으면 하는데... 저 숫자들은 왜 들어간거지...? 하고 말입니다. 그러면 보통 다시 데이터를 요청하기 보다는 셀프로 다시 구성하는 편이 몸은 힘들지만 시간과 정신적으로 편안합니다. ( 저는 소심해서 잘 부탁을 못하거든요 :-| )

그럴때마다 CONCATENATE, TEXTJOIN, CONCAT, SPLIT 같은 함수들을 써서 머리를 싸매고 필요한 텍스트를 뽑아내고 다시 조합해서 원하는 포맷으로 만들고 적용된 함수들을 제거하기 위해서 또... 아무튼 이것저것 복잡복잡하게 진행했었습니다. 손목을 가장 중시하는 저에겐 너무 가혹한 일이었습니다. 그래서!!!

이번 포스팅에서는 함수 없이!! 내가 원하는 포맷으로 텍스트를 추출해서 합치는 방법 을 소개 해드리겠습니다.

 

---

이번 포스팅을 읽고나면 그 동안의 고생이

너무나도 억울할 수 있으니 주의해주세요.

1) 원본 데이터 입력하기

작업에 사용할 데이터를 입력해보겠습니다.

일정한 규칙을 가지고 있는 데이터가 필요합니다.

저는 도로명 주소와 트와이스 멤버 이름을 매칭해보았습니다.

2) 원하는 포맷으로 텍스트 합치기 ( CONCAT 함수 대체하기 )

CONCAT 함수의 결과값처럼 텍스트를 합쳐보겠습니다.

CONCAT 함수 -> [아.. 엑셀좀 해놓을껄]엑셀에서 CONCAT 함수로 텍스트 합치기

CONCAT 함수는 단순히 모든 텍스트를 다 붙여주는 포맷을 가지고 있습니다.

CONCAT 포맷: {순번}{이름}{주소}{위치}

텍스트 포맷에 맞게 한땀한땀 텍스트를 입력해줍니다.

오른쪽 하단의 점을 누르고 필요한 곳까지 데이터를 채워넣습니다.

으잉? 이게 뭔가요? 하실텐데 조금만 더 참아주세요 :)

우측 하단에 생기는 작은 아이콘을 눌러서 빠른 채우기 버튼을 눌러줍니다.

3) 짜라란!

원하는 기능이 동작하는지 확인합니다.

4) 원하는 포맷으로 텍스트 합치기 ( CONCATENATE, TEXTJOIN 함수, & 대체하기 )

포맷을 정하고 텍스트를 합쳐보겠습니다.

CONCATENATE 함수 -> [아.. 엑셀좀 해놓을껄]엑셀에서 CONCATENATE 함수로 텍스트 합치기

TEXTJOIN 함수 -> [아.. 엑셀좀 해놓을껄]엑셀에서 TEXTJOIN 함수로 텍스트 합치기

아래 포맷을 통해 CONCATENATE, TEXTJOIN 함수와 & 기호를 대체할 수 있는지 확인해보겠습니다.

텍스트 포맷: {순번}. {이름} => {주소} <{위치}>

텍스트 포맷에 맞게 한땀한땀 텍스트를 입력해줍니다.

오른쪽 하단의 점을 누르고 필요한 곳까지 데이터를 채워넣습니다.

우측 하단에 생기는 작은 아이콘을 눌러서 빠른 채우기 버튼을 눌러줍니다.

5) 짜라란! x2

원하는 기능이 동작하는지 확인합니다.

6) 원하는 포맷으로 텍스트 합치기 ( CONCATENATE + LEFT, RIGHT, MID 함수 등 대체하기 )

텍스트 내의 문자를 추출하여 합쳐보겠습니다.

LEFT 함수 -> [아.. 엑셀좀 해놓을껄]엑셀에서 LEFT 함수로 텍스트 추출하기

RIGHT 함수 -> [아.. 엑셀좀 해놓을껄]엑셀에서 RIGHT 함수로 텍스트 추출하기

MID 함수 -> [아.. 엑셀좀 해놓을껄]엑셀에서 MID 함수로 텍스트 추출하기

LEFT, RIGHT MID 함수들로 텍스트를 나누고 ...

다시 CONCATENATE 함수로 텍스트를 합치고 ...

더 이상 이렇게 어렵게 작업할 필요가 없습니다.

아래 텍스트 포맷대로 실습을 해보면서 사실인지 확인해보겠습니다.

CONCAT 포맷: {순번}. {이름} {도시명} {위치}

텍스트 포맷에 맞게 한땀한땀 텍스트를 입력해줍니다.

오른쪽 하단의 점을 누르고 필요한 곳까지 데이터를 채워넣습니다.

우측 하단에 생기는 작은 아이콘을 눌러서 빠른 채우기 버튼을 눌러줍니다.

7) 짜라란! x3

원하는 기능이 동작하는지 확인합니다.

정말 신기하지 않나요?

엑셀이 규칙성을 찾아서 필요한 데이터를 채워준다니 말입니다.(저만 놀랍나요?ㅎㅎ)

그럼 앞으로 concat, concatenate, left, right 등등 함수들은 안써도 되나요?

라는 질문에는 상황에 따라 다르다 라고 대답해드리고 싶습니다.

샘플 데이터를 만들면서 강조해드렸다시피 이 기능은 규칙성을 가져야만 정확하게 동작을 합니다.

만약 규칙성이 없는 데이터라면 위에 나열한 함수들을 활용하셔야 한답니다.

---

이번 편에서는 간단하게 함수 없이 텍스트를 추출하고 합치는 방법에 대해 소개해드렸습니다.

이제 데이터에 규칙성이 보이면 함수를 사용기보단 이 방법을 사용해보시기 바랍니다.

알고보면 어렵지 않지만 막상 방법이 떠오르지 않아 고생하셨던 분들에게 도움이 됐으면 합니다.