엑셀) 왼쪽, 오른쪽, 중간 데이터 추출하기(쌍따옴표, 작은 따옴표, 대괄호 사이 데이터 등등)

※ 업무하면서 습득한 내용들을 정리해 놓은 포스팅입니다 :P 추가로 궁금하신 점은 댓글로 남겨주시고 필요한 자료 있으면 요청주세요! 잘못된 내용이 있으면 고쳐주시면 감사하겠습니다. 자료 퍼가실 때는 출처 남겨주세요!

---

 ο 목차

 

---

보안 로그를 보다보면 엑셀로 파일을 받은 다음 분석해야 하는 경우가 있습니다.

 

"IP:포트[국가]" 이런 식으로 하나의 셀 안에 있는 데이터를 IP, 포트, 국가 데이터로 각각 분리하면 필터 처리하기가 편하고 중복된 데이터를 없앨 수 있어서 로그 분석에 드는 시간이 줄여줍니다. 

 

이번 포스팅에서는 특정 기호의 왼쪽 데이터를 추출하는 방법과 특정 기호의 오른쪽 데이터를 추출하는 방법, 그리고 대괄호/쌍따옴표/작은따옴표 등 특정 문자 사이의 데이터를 추출하는 방법을 공유하려고 합니다.

 

많은 도움이 되길 바랍니다. 추출 함수 필요하신 분들은 아래 내용 참고 해주세요 :P

---

1. 특정 문자의 왼쪽 데이터 추출하는 수식

=LEFT(셀주소,FIND("특정문자",셀주소)-1)

※ 셀주소 = 추출할 문자를 포함하고 있는 셀주소

 

- 특정 문자를 포함한 왼쪽 데이터를 추출하려면 아래와 같이 사용

=LEFT(셀주소,FIND("특정문자",셀주소))

 

예시) J4 = IP:포트번호 일 때 아래 함수를 사용하면 IP만 추출 가능

=LEFT(J4,FIND(":",J4)-1)

 

2. 특정 문자의 오른쪽 데이터를 추출하는 수식

=RIGHT(셀주소,LEN(셀주소)-FIND("특정문자",셀주소))

※ 셀주소 = 추출할 문자를 포함하고 있는 셀주소

 

- 특정 문자를 포함한 오른쪽 데이터를 추출하려면 아래와 같이 사용

=RIGHT(셀주소,LEN(셀주소)-FIND("특정문자",셀주소)+1)

 

예시) J4 = IP:포트번호 일 때 아래 함수를 사용하면 포트번호만 추출 가능

=RIGHT(J4,LEN(J4)-FIND(":",J4))

 

3. 대괄호 안의 데이터를 추출하는 수식

=MID(LEFT(셀주소,FIND("]",셀주소)-1),FIND("[",셀주소)+1,LEN(셀주소))

※ 셀주소 = 추출할 문자를 포함하고 있는 셀주소

※ 대괄호가 1개 있는 경우 위의 수식 그대로 사용하면 대괄호 안의 데이터만 추출 가능

 

예시) J4 = IP:포트번호[국가] 일 때 아래 함수를 사용하면 국가만 추출 가능

=MID(LEFT(J4,FIND("]",J4)-1),FIND("[",J4)+1,LEN(J4))

 

4. 쌍따옴표 안의 데이터를 추출하는 수식

=MID(LEFT(셀주소,FIND("A",SUBSTITUTE(셀주소,"""","A",LEN(셀주소)-LEN(SUBSTITUTE(셀주소,"""",""))))-1),FIND("""",셀주소)+1,LEN(셀주소))

※ 셀주소 = 추출할 문자를 포함하고 있는 셀주소

※ 수식의 셀주소 부분을 변경하여 쌍따옴표 안의 데이터 추출 가능

※ 쌍따옴표는 엑셀에서 문자를 감싸는데 쓰이고 이스케이프(escape) 기호로도 쓰임. 따라서 수식에서 쌍따옴표를 인식하기 위해서는 """" 이렇게 써줘야 함. ("" : 문자를 감싸는데 사용, " : 쌍따옴표를 이스케이트하기 위해 사용, " : 사용할 쌍따옴표)

 

5. 작은 따옴표 안의 데이터를 추출하는 함수

- 이 수식은 같은 문자(기호) 사이의 데이터를 추출하는데도 사용 가능

=MID(LEFT(셀주소,FIND("A",SUBSTITUTE(셀주소,"'","A",LEN(셀주소)-LEN(SUBSTITUTE(셀주소,"'",""))))-1),FIND("'",셀주소)+1,LEN(셀주소))

※ 셀주소 = 추출할 문자를 포함하고 있는 셀주소

 

- 기본적으로 수식의 셀주소 부분을 변경하여 작은 따옴표 사이의 데이터를 추출할 수 있으며 수식의 "'" 부분을  "특정문자(기호)"로 변경하면 특정문자(기호) 사이의 데이터도 추출 가능

=MID(LEFT(셀주소,FIND("A",SUBSTITUTE(셀주소,"특정문자","A",LEN(셀주소)-LEN(SUBSTITUTE(셀주소,"특정문자",""))))-1),FIND("'",셀주소)+1,LEN(셀주소))

---

위의 수식들은 데이터의 길이와 상관없이 데이터를 추출할 수 있다는 장점이 있습니다.

 

데이터의 길이를 알면 더 쉬운 수식을 사용해서 데이터를 추출할 수 있으니 굳이 어려운 방법을 쓸 필요는 없지만, 데이터의 길이가 들쭉날쭉하다면 위의 수식을 사용하는게 훨씬 좋습니다.

 

적게 일하고 최대 효율 뽑으세요 여러분 :P 화이팅!

---

※ 좋아요와 구독은 큰 힘이 됩니다. 감사합니다.