엑셀에서 별표만 지우고 나머지 내용은 남기는 법

받은 목록에 붙은 별표는 최신 항목 표시일 수도 있고 검토가 끝나지 않았다는 뜻일 수도 있다. 표시를 빼고 품목명을 비교하려는 작업이라면 이름은 남아야 한다. 그런데 엑셀의 찾을 내용에 *를 그대로 넣으면 ‘별표라는 글자’가 아니라 여러 글자를 대신하는 검색 조건이 된다.

먼저 없애려는 표시의 뜻을 확인하고 원본 파일을 남긴다. 별표가 미확정 금액을 뜻한다면 정리본에서 표시를 지워도 그 금액이 확정되는 것은 아니다. Windows 데스크톱 Excel에서 단순한 문자 목록은 찾기·바꾸기로 손볼 수 있다. 수식까지 들어 있다면 원본 옆에 정리 결과를 만드는 편이 전후를 비교하기 쉽다.

별표만 지우는 찾기·바꾸기 입력

찾을 내용에는 별표 앞에 물결표를 붙인 ~*를 입력한다. 바꿀 내용은 빈칸으로 둔다. 물음표를 없애려면 ~?, 물결표 자체를 없애려면 ~~를 찾는다. Microsoft의 와일드카드 설명에서 정한 문자 검색 방식이다.

*는 글자 수에 관계없이 문자열을 찾고 ?는 한 글자를 대신한다. 따라서 별표를 빈 문자열로 바꾼다고 생각하고 *를 검색하면, 표시뿐 아니라 이름 전체까지 바뀔 수 있다. 이미 그런 결과가 나왔다면 다른 편집을 이어가기 전에 Ctrl+Z로 되돌린다. 실행 취소가 불가능한 상태에서는 이 조작만으로 원본이 복구되지 않는다.

수식이 없는 문자 목록을 정리할 때는 작업 복사본에서 다음 순서로 진행한다. 계산식이 섞였는지 확실하지 않으면 먼저 아래의 수식 확인 절을 읽고 보조 열 방식을 선택한다.

1. 별표를 지울 목록의 셀 범위를 선택하고 Ctrl+H를 누른다.
2. 찾을 내용에 ~*를 넣는다. 바꿀 내용에는 공백 한 칸도 넣지 않고 완전히 비운다.
3. 옵션을 열어 범위를 시트로 두고, 이름 중간이나 끝의 별표를 지울 것이므로 전체 셀 내용 일치는 끈다. 별도의 서식 조건이 걸려 있는지도 살핀다.
4. 다음 찾기로 해당 셀을 보고 바꾸기를 한 번 실행한다. 필터*가 필터로 바뀌었는지 확인한 뒤 나머지에 모두 바꾸기를 적용한다.

바꿀 내용에 스페이스를 넣으면 별표 자리에는 보이지 않는 공백이 남는다. 화면에서는 비슷해 보여도 뒤에서 품목명을 비교할 때 다른 문자열이 될 수 있다. 반대로 별표를 다른 표시로 바꾸려는 작업이라면 빈칸 대신 그 문자를 넣는다. ‘없애기’와 ‘공백으로 대체하기’는 다른 작업이다.

모두 바꾸기 전에 찾은 셀을 살피는 이유는 검색어가 맞아도 작업 대상이 틀릴 수 있기 때문이다. 품목명만 손보려는데 담당자 메모까지 나왔다면 적용을 멈추고 범위를 다시 잡는다. 찾기·바꾸기 도움말의 모두 찾기로 셀 목록을 확인하거나, 정리할 목록만 별도 작업 시트로 복사해 작업할 수 있다.

코드 하나만 찾을 때는 전체 셀 일치도 켠다

별표가 코드의 일부라면 지우지 않고 정확히 찾는 일이 먼저다. 예를 들어 P-17*를 찾아야 하는데 P-170*까지 나온다면 찾을 내용에 P-17~*를 넣고 전체 셀 내용 일치를 켠다. 여기서 물결표는 별표를 실제 문자로 읽게 하고, 전체 셀 일치는 앞뒤에 다른 글자가 붙은 항목을 제외한다.

전체 셀 일치만 켜고 P-17*를 검색하는 것은 다르다. 별표가 여전히 여러 글자를 대신하므로 P-170*도 조건에 맞을 수 있다. 실제로 Microsoft Q&A의 외부 데이터베이스 코드 질문에서도 전체 셀 일치를 켰는데 두 코드가 함께 검색됐다. 셀의 경계와 검색 기호의 뜻을 각각 지정해야 해결되는 문제다.

반대로 목록에서 별표만 모두 지울 때 전체 셀 일치를 켜 두면 필터* 같은 셀은 빠진다. 셀 전체가 별표 하나인 항목을 찾는 조건이 되기 때문이다. 같은 파일에서도 ‘코드 전체를 찾기’에서 ‘코드 안의 표시를 없애기’로 작업을 바꾸면 이 옵션을 다시 본다. 작은따옴표나 큰따옴표로 검색어를 감싸는 방법으로 대신하지 않는다.

수식에 들어 있는 별표는 지우지 않는다

~*는 별표를 정확히 찾게 할 뿐, 그 별표가 메모인지 계산 기호인지 판단하지 않는다. 수식 =3*4의 별표는 곱셈이다. 이것을 삭제해 =34가 되면 오류 메시지 없이도 결과가 12에서 34로 바뀐다. 수식의 연산자를 지운 결과를 보여 주는 간단한 예다.

화면에 별표가 안 보여도 수식 안에는 들어 있을 수 있다. 찾기 탭에서는 값을 검색할 수 있지만 Windows Excel의 바꾸기 탭에서 찾는 위치는 수식이다. 따라서 ‘화면에서 별표가 있는 것만 찾았으니 바꾸기도 그 글자만 고치겠지’라고 이어서 실행하면 안 된다. 계산 결과와 수식 본문이 서로 다른 검색 대상이다.

인계받은 표라면 정리할 범위를 선택하고 Ctrl+G → 옵션 → 수식으로 수식 셀을 찾아볼 수 있다. 이동 옵션의 수식 선택은 지정한 영역에서 계산식이 들어 있는 셀을 찾는다. 셀을 눌렀을 때 위쪽 수식 입력줄에 무엇이 있는지도 함께 본다.

수식 입력줄을 보는 데에는 또 다른 이유가 있다. 셀에는 1200*가 보이는데 입력줄에는 숫자 1200만 있다면, 사용자 지정 표시 형식이 별표를 붙였을 수 있다. 예를 들어 0"*"는 숫자 뒤에 별표를 표시한다. 이때는 지울 글자가 값에 들어 있지 않다. 홈의 표시 형식 그룹에서 대화상자를 열고 사용자 지정의 형식 코드를 확인한다. 이 예에서는 0"*"를 0으로 고치면 숫자를 바꾸지 않고 표시만 없앤다.

예컨대 A열에 상품명이 있고 B열에 수량과 단가를 곱하는 계산식이 있다면 두 열을 함께 정리할 이유가 없다. A열에도 다른 셀에서 이름을 가져오는 수식이 섞여 있다면, 원본을 직접 고치기보다 다음 절의 보조 열에서 표시만 뺀 결과를 만든다. 원래 계산식은 계속 남고 정리된 이름을 옆에서 비교할 수 있다.

원본 옆에서 지우기 전후를 비교한다

A열을 원본으로 남기고 빈 B열의 제목을 ‘정리된 이름’으로 적는다. B2에 =SUBSTITUTE(A2,"*","")를 입력한 뒤 필요한 마지막 행까지 채운다. 이때는 찾기 창의 ~*를 옮겨 넣지 않는다. SUBSTITUTE에는 바꿀 문자열 자체인 "*"를 지정한다.

SUBSTITUTE 함수는 주어진 문자열을 새 문자열로 바꾼다. 이 수식은 A2의 원래 내용이나 수식 본문을 편집하지 않고, A2가 내놓은 값에서 별표를 뺀 문자열을 B2에 표시한다. 원본이 단순 문자든 다른 수식의 결과든 B열에서 비교할 수 있다는 점이 바로 바꾸기와 다르다.

모든 별표를 지울 생각이 아니라면 결과를 보고 규칙을 좁힌다. =SUBSTITUTE(A2,"*","",1)은 첫 번째 별표만 지우므로 필터*임시*를 필터임시*로 만든다. 네 번째 인수의 1은 왼쪽부터 만난 첫 번째라는 뜻이며, 무조건 마지막 글자를 지운다는 뜻이 아니다.

목록마다 별표의 위치와 의미가 다르다면 한 가지 수식으로 밀어붙이기 전에 몇 행을 나란히 읽는다. 최신 상품 표시를 빼고 동일 상품을 모으려는지, 서로 다른 옵션을 구별하는 기호까지 없애는지에 따라 결과의 의미가 달라진다. 필터*와 필터가 같은 이름이 된 뒤 자동으로 중복 제거까지 하는 것은 이번 문자 정리와 별도의 판단이다.

금액은 계산하고, 코드는 그대로 남긴다

별표를 없앤 결과가 1200처럼 보여도 SUBSTITUTE가 만든 것은 문자열이다. 이 열을 금액 합계에 쓸 것이라면 =VALUE(SUBSTITUTE(A2,"*",""))로 숫자로 바꾼 결과를 만든다. 문자로 저장된 숫자의 변환처럼, 금액을 계산에 사용할 형태로 바꾸는 단계가 추가되는 것이다.

가상 목록에 1200*와 800*가 들어 있다면 두 결과가 숫자 1200과 800인지 보고 합계 2000과 대조한다. 별표 제거는 문자열 정리이고, 합계가 기대대로 계산되는지는 그다음 확인이다. 셀의 글꼴이나 정렬 방향만으로 계산 가능한 숫자라고 판단하지 않는다.

SUBSTITUTE로 별표만 뺀 결과를 ‘값’으로 붙여넣어도 금액이 숫자로 바뀌지는 않는다. 제출받은 사람이 합계를 낼 목록이라면 VALUE로 변환한 금액을 대조한 뒤 그 결과를 옮긴다. 반대로 코드 00120은 숫자로 바꾸지 않고 문자 결과를 옮겨야 앞자리 0이 남는다.

자료에 미정*도 섞였다면 별표를 지운 ‘미정’은 숫자가 아니다. VALUE가 해석할 수 없는 문자열은 #VALUE! 오류가 된다. 이런 행을 오류 처리 수식으로 전부 0으로 바꾸면 아직 모르는 금액을 0원으로 보고하게 된다. 원본 행을 남겨 담당자에게 값을 확인하고, 확정된 금액과 나란히 비교한다.

상품코드에는 같은 숫자 변환을 적용하지 않는다. 00120*에서 필요한 결과는 코드 00120이지 숫자 120이 아니다. 번호를 텍스트로 보관하는 이유도 여기에 있다. 코드 열은 SUBSTITUTE 결과를 유지하고, 금액 열에만 VALUE를 적용하면 문자 정리 때문에 식별번호까지 바뀌는 일을 피할 수 있다.

제출본에는 정리된 값을 옮긴다

보조 열은 원본을 참조하므로 A열을 고치면 B열 결과도 다시 계산된다. 앞으로도 같은 파일에서 관리할 목록이라면 이 연결을 남겨 둔다. 반면 이번에 정리한 품목명만 다른 파일로 전달하려면 결과 범위를 복사하고 제출용 시트에서 홈 → 붙여넣기 → 값을 선택한다.

이 방식은 수식을 계산 결과로 고정한다. 수식 대신 값이 들어가므로 받는 사람이 원본 열을 갖고 있지 않아도 된다. 대신 나중에 원본의 오타를 고쳐도 이미 붙여넣은 제출본은 바뀌지 않는다. 원본과 계산용 보조 열은 작업 파일에 보존하고, 전달할 결과만 별도로 만든다.

마지막으로 원본과 제출본에서 별표가 하나 있던 행, 여러 개 있던 행, 별표가 없던 행을 골라 비교한다. 코드 열에는 앞자리 0이 남았는지, 금액 열에는 미정인 행을 0원으로 바꿔 넣지 않았는지 본다. 별표가 사라졌다는 사실만으로 목록 정리가 끝난 것은 아니다. 제출본에서 품목을 다시 찾고 금액을 더할 수 있어야 이번 작업이 끝난다.