[엑셀] (SEARCH함수) 특정문자 포함한 행만 추출

USA adress List.xls

0.46MB

엑셀로 SEARCH 함수를 사용하여 특정문자만 포함한 행을 추출하여 별도의 시트에 정리해 보았어요

이 함수의 경우, 특정지역 or 주소록에서 어떤 시나 구, 동 또는 미국내 해당주만 뽑아서 dm 발송시 주소 뽑는것도 편하고, 곧 연말연초인데 회사에서는 특정 지정 거래처들만 뽑아서 주소록 만들기도 편한 함수 같아요

 

저는 세계 여러나라 중 미국의 텍사스주에서 열리는 전시회에  참여하기 위해 텍사스주에 있는 관련회사에만 팜플렛을 보내기 위해, 주소란에 "TX" 문자가 포함된 회사들만 별도의 시트에 아래처럼 추출했어요.

 

SEARCH 함수로 특정문자 포함한 행만 별도의 시트로 추출

 

이제 SEARCH 함수식이 어떻게 사용되었는지 상세히 보려고 합니다.

 

1. TEXAS 의 약자인 "TX'를 포함한 내용만 찾아야 하므로 이렇게 기재해 줍니다. 대소문자 구별하지 않습니다.

=SEARCH("TX",USA!$D:$D)

 

2. "TX"를 포함한 행이 TRUE 이라는 걸 표시해주는 ISNUMBER를 사용해 줍니다.

=ISNUMBER(SEARCH("TX",USA!$D:$D))

 

3. INDEX를 사용하여 해당 문자열이 포함되어진 행만을 별도의 시트로 가져옵니다.

=INDEX(USA!A:A,1/LARGE(INDEX((ISNUMBER(SEARCH("TX",USA!$D:$D)))/ROW(USA!A:A),),ROW()-1))

 

4. 값이 달라서 에러가 나오는 칸은 빈칸으로 보이도록 IFERROR를 사용해 마무리 해줍니다.

=IFERROR(INDEX(USA!A:A,1/LARGE(INDEX((ISNUMBER(SEARCH("TX",USA!$D:$D)))/ROW(USA!A:A),),ROW()-1)),"")

 

5. A2 에 하기와 같이 식을 입력 후 왼쪽으로 E열까지 드래그하고, 아래로도 쭉 드래그 하면, "TX"를 포함한 행만 추출되어져 표시되어집니다.

 

A2 : =IFERROR(INDEX(USA!A:A,1/LARGE(INDEX((ISNUMBER(SEARCH("TX",USA!$D:$D)))/ROW(USA!A:A),),ROW()-1)),"")

A3 : =IFERROR(INDEX(USA!A:A,1/LARGE(INDEX((ISNUMBER(SEARCH("TX",USA!$D:$D)))/ROW(USA!A:A),),ROW()-1)),"")

B2 : =IFERROR(INDEX(USA!B:B,1/LARGE(INDEX((ISNUMBER(SEARCH("TX",USA!$D:$D)))/ROW(USA!B:B),),ROW()-1)),"")

C2 : =IFERROR(INDEX(USA!C:C,1/LARGE(INDEX((ISNUMBER(SEARCH("TX",USA!$D:$D)))/ROW(USA!C:C),),ROW()-1)),"")

D2 : =IFERROR(INDEX(USA!D:D,1/LARGE(INDEX((ISNUMBER(SEARCH("TX",USA!$D:$D)))/ROW(USA!D:D),),ROW()-1)),"")

E2 : =IFERROR(INDEX(USA!E:E,1/LARGE(INDEX((ISNUMBER(SEARCH("TX",USA!$D:$D)))/ROW(USA!E:E),),ROW()-1)),"")

 

 

 

참고로,

미국 각 주의 약자를 아래처럼 정리해 보았습니다.

alabama	AL
alaska	AK
arizona	AZ
arkansas	AR
california	CA
colorado	CO
coonnecticut	CT
delaware	DE
florida	FL
georgia	GA
hawai	HI
IDAHO	ID
ILLINOIS	IL
INDIANA	IN
IOWA	IA
KANSAS	KS
KENTUCKY	KY
LOUSIANA	LA
MASSACHUSETTS	MA
MAINE	ME
MARYLAND	MD
MICHIGAN	MI
MINNESOTA	MN
MISSISSIPPI	MS
MISSOURI	MO
MONTANA	MT
NEBRASKA	NE
NEVADA	NV
NEW HAMPSHIRE	NH
NEW JERJEY	NJ
NEW MEXICO	NM
NEWYORK	NY
NORTH CAROLINA	NC
NORTH DAKOTA	ND
OHIO	OH
OKLAHOMA	OK
OREGON	OR
PENNSYLVANIA	PA
rhode island	RI
SOUTH DAKOTA	SC
SOUTH CAROLINA	SD
TENNESSEE	TN
TEXAS	TX
UTAH	UT
vermont	VT
virginia	VA
WASHINGTON	WA
WASHINGTON D.C.	DC
WEST VERGINIA	WV
WISCONSIN	WI
WYOMING	WY