Excel 에서 문자의 첫 번째， 마지막 또는 발생 횟수 발생 위치를 어떻게 찾을 수 있나요？
다양한 데이터 처리 작업에서 하이픈("-“)과 같은 특수 문자를 포함하는 텍스트 문자열 목록을 다룰 때 해당 문자의 첫 번째, 마지막 또는 특정(n 번째) 발생 위치를 파악해야 할 경우가 많습니다. 예를 들어 구분 기호를 기준으로 정보를 추출하거나 부품 코드를 분석하거나 데이터 구성 요소를 분리할 때 문자열 내 특정 문자가 어디에 위치하는지를 정확히 파악해야 합니다. 그러나 Excel 에는 셀 내 문자의 첫 번째， 마지막 또는 발생 횟수 발생 위치를 직접 반환하는 기본 제공 함수가 없습니다。 이 문서에서는 이러한 발생 위치를 정확하게 찾을 수 있는 여러 가지 실용적인 기법과 단계별 솔루션을 소개하여 다양한 데이터 구문 분석 및 준비 요구사항을 지원합니다。
수식을 사용하여 문자의 마지막 발생 위치 찾기
특정 문자(예: “-”)의 마지막 발생 위치를 반환하려는 경우, Excel 의 표준 함수만으로는 이를 직접 수행할 수 없습니다。 그러나 몇 가지 수식을 조합하면 이 문제를 효율적으로 해결할 수 있습니다。 제품 코드， 파일 경로 또는 일관된 구분 기호 패턴을 따르는 기타 데이터를 처리할 때 유용한 두 가지 수식 접근 방식을 아래에 소개합니다。 복잡한 수식은 대규모 데이터셋에서 계산 부하를 증가시킬 수 있으므로 주의하시기 바랍니다。
1。 빈 셀(예： C2)에 다음 수식 중 하나를 입력하거나 복사합니다：
=SEARCH("^^",SUBSTITUTE(A2,"-","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))=LOOKUP(2,1/(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)="-"),ROW(INDIRECT("1:"&LEN(A2))))두 수식 모두 참조 셀(A2)에서 문자 “-”의 마지막 발생 위치를 숫자로 반환하도록 설계되었습니다。 첫 번째 수식은SEARCH함수와SUBSTITUTE함수를 사용하여 마지막 “-”를 고유한 기호로 바꾼 후 해당 기호를 검색하여 위치를 식별합니다。 두 번째 수식은LOOKUP함수를MID및ROW함수와 함께 사용하여 마지막 “-”를 찾습니다。 사용자의 숙련도나 데이터 양에 따라 두 방법 중 하나를 선택할 수 있습니다。LOOKUP방식은 배열 기능을 지원하는 최신 Excel 버전에서 더 쉽게 적용할 수 있습니다。
2。 수식을 입력한 후Enter키를 누릅니다。 이 수식을 추가 행에 적용하려면 필요에 따라 채우기 핸들을 아래로 드래그하여 전체 범위를 채웁니다。 그러면 각 셀에 해당 텍스트 문자열의 마지막 발생 위치가 즉시 입력됩니다(아래 이미지 참조)。 수식을 다른 영역에 복사할 때 참조 셀이 올바른지 다시 확인하시기 바랍니다。
참고：이 예제에서A2는 데이터가 포함된 셀을 나타내며， “-”는 찾을 대상 문자입니다。 “-”는 검색하려는 임의의 문자로 변경할 수 있습니다。 셀이 비어 있거나 텍스트에 해당 문자가 없으면 수식이 오류를 반환할 수 있습니다。 반복되는 구분 기호나 특수 기호가 포함된 텍스트 항목의 경우 결과에 영향을 줄 수 있는 숨겨진 문자나 출력되지 않는 문자가 없는지 주의 깊게 확인하시기 바랍니다。
사용자 정의 함수를 사용하여 문자의 마지막 발생 위치 찾기
또 다른 유연한 방법은 VBA 모듈에서 사용자 정의 함수(UDF)를 사용하는 것입니다。 이 방법은 다양한 분석을 위해 자주 특정 문자의 마지막 발생 위치를 찾아야 할 경우 더 많은 제어 기능과 재사용성을 제공합니다。 이 접근 방식은 내장 Excel 함수와 유사하게 작동하는 간결한 수식 인터페이스()=lastpositionofchar(cell, character))를 원할 때 이상적입니다. 다만 VBA 솔루션을 사용하려면 매크로를 활성화해야 하며, UDF 를 보존하려면 항상 통합 문서를 매크로 사용 파일(*。xlsm)로 저장해야 합니다。
1。 이 함수를 추가하려는 워크시트를 엽니다。
2.ALT + F11키를 동시에 눌러Microsoft Visual Basic for Applications편집기를 엽니다。
3。 VBA 편집기에서삽입>모듈을 클릭하여 새 모듈을 생성합니다。 그런 다음 다음 코드를 모듈 창에 복사하여 붙여넣습니다。
VBA 코드： 문자의 마지막 발생 위치 찾기
Function LastpositionOfChar(strVal As String, strChar As String) As Long
LastpositionOfChar = InStrRev(strVal, strChar)
End Function
문제 해결：함수가 결과를 반환하지 않으면 매크로가 활성화되어 있고 수식에 올바른 셀 참조와 문자를 입력했는지 확인하세요。 다른 문자의 마지막 발생 위치를 찾아야 할 경우 인수를 적절히 변경하면 됩니다。
4。 코드 창을 닫습니다。 워크시트로 돌아가서 빈 셀(예： B2)에 다음 수식을 입력합니다：=lastpositionofchar(A2,"-")。 필요에 따라A2를 적절한 셀로， “-”를 대상 문자로 변경합니다。
5。 필요에 따라 채우기 핸들을 아래로 드래그하여 수식을 추가 셀에 적용합니다。 그러면 아래 그림과 같이 각 텍스트 문자열에서 지정한 문자의 마지막 위치가 반환됩니다。
참고： 첫 번째 인수()A2)는 텍스트 문자열이 포함된 원본 셀이고， 두 번째 인수(“-”)는 대상 문자입니다。 필요에 따라 이러한 매개변수를 조정할 수 있습니다。 일치 항목이 없으면 함수가0를 반환할 수 있습니다。 VBA 코드를 실행하거나 편집하기 전에는 항상 작업 내용을 저장하세요。 오류가 발생하면 따옴표 위치나 셀 참조가 잘못되었는지 확인하시기 바랍니다。
수식을 사용하여 문자의 첫 번째 또는 발생 횟수 발생 위치 찾기
텍스트 문자열에서 마지막이 아니라 첫 번째, 두 번째 또는 발생 횟수 발생 위치를 찾아야 하는 경우 다른 수식 구조를 사용할 수 있습니다. 이는 계층적 코드를 구문 분석하거나 구조화된 ID 의 일부를 추출하거나 데이터 항목에서 반복되는 기호를 식별할 때 자주 필요합니다。
1. 지정한 문자의 n 번째 위치를 얻으려면 빈 셀(예： C2)에 다음 수식을 입력하거나 붙여넣습니다：
=FIND(CHAR(160),SUBSTITUTE(A2,"-",CHAR(160),2))이 수식에서A2는 검색할 셀이고， “-”는 찾을 문자이며，2는 찾고자 하는 문자의 발생 순서(예: 2 는 두 번째 발생)를 나타냅니다。 데이터 요구사항에 맞게 발생 횟수를 조정할 수 있습니다。
2。 Enter 키를 누르세요Enter。 더 많은 행에 수식을 적용하려면 채우기 핸들을 원하는 범위까지 아래로 드래그하세요。 이렇게 하면 각 텍스트 문자열에서 예를 들어 “-”의 두 번째 발생 위치를 얻을 수 있습니다。 지정한 발생 횟수가 문자열 내 해당 문자의 총 개수보다 크면 수식은 오류를 반환합니다。 이 경우 텍스트 데이터나 발생 횟수 매개변수를 확인하세요。
참고：수식의 숫자2는 검색할 발생 인스턴스입니다。 이 숫자를 조정하여 지정된 문자의 첫 번째， 세 번째， 네 번째 또는 다른 임의의 발생 위치를 찾을 수 있습니다。 지정한 만큼 문자가 발견되지 않으면 #VALUE！ 오류가 표시됩니다。 이 경우IFERROR()함수를 사용하여 출력 결과를 깔끔하게 처리할 수 있습니다。
간편한 기능으로 특정 문자의 첫 번째 또는 발생 횟수 발생 위치 찾기
복잡한 수식이나 VBA 코드 작성이 어렵거나 직접적이고 대화형 솔루션을 선호하는 사용자를 위해Kutools for Excel에는문자열에서 N 번째 발생 위치 찾기라는 편리한 도구가 제공됩니다。 이 유틸리티를 사용하면 셀 내 특정 문자의 첫 번째， 두 번째， 세 번째 또는 임의의 발생 횟수 발생 위치를 몇 번의 클릭만으로 쉽게 추출할 수 있어 반복적인 데이터 정리 작업이나 대용량 데이터셋 처리 시 시간을 크게 절약할 수 있습니다。
Kutools for Excel 설치 후Kutools for Excel다음 단계를 따라 빠르게 결과를 얻을 수 있습니다：
텍스트 문자열 집합에서 하이픈 문자 “-”의 두 번째 발생 위치를 얻고자 한다고 가정해 보겠습니다：
1。 결과를 표시할 셀을 클릭하세요。
2.Kutools>함수 도우미>함수 도우미메뉴에서 아래 그림과 같이 선택하세요：
3.함수 도우미대화 상자에서：
- 리본 메뉴에서조회를 선택합니다함수 유형드롭다운 목록。
- 문자열에서 N 번째 발생 위치 찾기문자열에서 N 번째 발생 위치 찾기에서함수 선택목록을 선택합니다。
- 인수 입력인수 입력영역에서 텍스트가 포함된 셀을 선택하고 찾을 문자를 입력한 다음 발생 횟수를 지정합니다。
4.확인。 수식이 결과를 반환한 후에는 채우기 핸들을 아래로 드래그하여 다른 행에도 필요에 따라 적용할 수 있습니다。 이 방법을 사용하면 수동 입력 시 발생할 수 있는 수식 오류 위험을 줄일 수 있습니다。
Kutools for Excel 지금 다운로드하고 무료로 체험하세요！
이 옵션은 비기술적 사용자나 여러 워크시트에서 자주 반복 작업을 수행해야 하는 사용자에게 특히 적합하며 수동 오류를 최소화하고 복잡한 수식 작성에 소요되는 시간을 절약할 수 있습니다。
최신 Excel 버전(동적 배열 지원)용 대체 수식
동적 배열을 지원하는 최신 Excel 버전(Excel 365, Excel 2021 이상)에서는SEQUENCE및FILTER같은 함수를 활용하여 문자의 모든 발생 위치를 한 번에 반환할 수 있습니다。 이를 통해 발생 횟수 을 쉽게 식별하거나 추가 처리를 위해 모든 발생 위치를 별도 범위에 나열할 수도 있습니다。
1. 셀 A2 에 포함된 문자 “-”의 모든 발생 위치를 얻으려면 빈 셀(예： B2)에 다음 수식을 입력하세요：
=FILTER(SEQUENCE(LEN(A2)), MID(A2,SEQUENCE(LEN(A2)),1)="-")Enter 키를 누른 후Enter수식은 “-”의 각 발생 위치를 인접한 셀에 자동으로 확장합니다。 만약 발생 횟수 만 반환하려면 다음 수식을 사용하세요：
=INDEX(FILTER(SEQUENCE(LEN(A2)), MID(A2,SEQUENCE(LEN(A2)),1)="-"),2)2 을2원하는 발생 횟수로 바꾸세요。 이 방법은 길이가 가변적인 배열이나 목록에 특히 효과적이지만， 해당 함수를 지원하는 Excel 버전이 필요합니다。
팁：#CALC！ 또는 유사한 오류가 표시되면 Excel 버전이 동적 배열을 지원하는지 확인하고 참조된 셀에 텍스트 데이터가 포함되어 있는지 점검하세요。
문자의 발생 횟수 발생 위치를 찾는 VBA 코드
마지막 발생 위치뿐 아니라 임의의 발생 횟수 발생 위치를 찾아야 하는 경우 VBA 도 도움이 될 수 있습니다。 다음 매크로는 다양한 파싱 요구사항에 맞게 사용자 정의할 수 있으며 여러 워크북에서 반복적으로 사용할 때 시간을 절약해 줍니다。
1.개발자탭 >Visual Basic을 선택한 후삽입>모듈을 클릭하고 아래 코드를 모듈 창에 붙여넣으세요：
Function NthPositionOfChar(cell As Range, ch As String, nth As Integer) As Long
Dim s As String
Dim i As Long, count As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
s = cell.Value
count = 0
For i = 1 To Len(s)
If Mid(s, i, 1) = ch Then
count = count + 1
If count = nth Then
NthPositionOfChar = i
Exit Function
End If
End If
Next i
NthPositionOfChar = 0
End Function2。 편집기를 닫으세요。 워크시트에서 수식=NthPositionOfChar(A2,"-",2)(필요에 따라 인수 변경)를 사용하여 셀 A2 에서 “-”의 두 번째 발생 위치를 반환할 수 있습니다。 단， 발생 횟수 이 존재하지 않으면 함수는 0 을 반환함에 유의하세요。
이 코드는 데이터에서 유연한 구분자 정보를 추출하거나 반복적인 파싱 작업을 간소화하는 데 이상적입니다。 또한 VBA 사용자 정의 함수는 대소문자 구분 검색이나 대용량 데이터셋 처리 등 보다 특화된 요구사항에 맞게 조정할 수도 있습니다。
텍스트 문자열과 문자 발생 위치 작업 시 공백， 개행 또는 특수 유니코드 기호와 같은 숨겨진 비인쇄 문자가 수식 결과에 영향을 줄 수 있으므로 항상 확인하세요。 또한 데이터에 다양한 구분자가 사용되는 경우 수식이나 매크로 적용 전 일관성을 확보해야 합니다。 오류가 발생하면 입력 매개변수， 수식 참조， 그리고 사용 중인 함수와 호환되는 데이터 형식(텍스트/숫자)인지 다시 확인하세요。
수식， VBA， Kutools 기능을 함께 사용하면 일상적이고 고급적인 Excel 텍스트 처리 작업 모두에 포괄적인 유연성을 제공합니다。 기술적 숙련도， Excel 버전， 데이터 복잡성에 따라 가장 적합한 방법을 선택하여 정확성과 효율성을 극대화하세요。
관련된 추가 기사：
- Excel 에서 첫 번째/마지막 단어를 제외한 모든 단어 추출하기
- 셀에서 첫 번째 또는 마지막 단어를 제외한 모든 단어를 추출하면 필요한 단어만 남기고 불필요한 단어를 제거하는 데 도움이 됩니다. 이 경우 원하는 단어를 하나씩 복사하여 다른 셀에 붙여넣을 수도 있습니다. 그러나 여러 셀의 값을 모두 첫 번째 또는 마지막 단어를 제외하고 추출해야 한다면 매우 번거로울 수 있습니다. Excel 에서 첫 번째 또는 마지막 단어를 제외한 모든 단어를 빠르고 쉽게 추출하려면 어떻게 해야 할까요？
- 셀 내에서 오른쪽에서 왼쪽으로 문자 추출하기
- 이 문서에서는 Excel 워크시트에서 공백 문자가 나올 때까지 셀의 오른쪽에서 문자를 추출하여 다음과 같은 결과를 얻는 방법에 대해 설명합니다。 이 문서에서 소개하는 유용한 수식을 사용하면 이러한 작업을 빠르고 쉽게 해결할 수 있습니다。
- 앞/뒤 X 자 또는 특정 위치의 문자 제거하기
- 이 문서에서는 Excel 워크시트에서 공백 문자가 나올 때까지 셀의 오른쪽에서 문자를 추출하여 다음과 같은 결과를 얻는 방법에 대해 설명합니다。 이 문서에서 소개하는 유용한 수식을 사용하면 이러한 작업을 빠르고 쉽게 해결할 수 있습니다。
- 첫 번째 소문자의 위치 찾기
- 대소문자가 혼합된 텍스트 문자열 목록이 있고， Excel 워크시트에서 각 문자열에서 첫 번째 소문자가 나타나는 위치를 알고 싶을 때가 있습니다。 하나씩 세지 않고도 어떻게 빠르게 결과를 얻을 수 있을까요？
최고의 Office 생산성 도구
| 🤖 | KUTOOLS AI 도우미： 다음을 기반으로 데이터 분석 혁신하기：지능형 실행 | 코드 생성| 사용자 지정 수식 생성 | 데이터 분석 및 차트 생성| 향상된 함수 호출… |
| 인기 기능:찾기， 강조 표시 또는 중복 표시 | 빈 행 삭제 | 데이터 손실 없이 열 결합 또는 셀 제거 | 공식을 사용하지 않는 반올림... | |
| 슈퍼 LOOKUP:다중 조건 VLookup | 다중 값 VLookup | 여러 시트에서 VLookup | 퍼지 매치.... | |
| 고급 드롭다운 목록:드롭다운 목록 빠르게 생성 | 종속형 드롭다운 목록 | 다중 선택 드롭다운 목록.... | |
| 열 관리자:특정 수의 열 추가|열 이동|숨겨진 열의 표시 상태 전환|범위 및 열 비교... | |
| 주요 기능:그리드 포커스 | 디자인 보기 |향상된 수식 표시줄 | 워크북 및 시트 관리자 | 자원 라이브러리(자동 텍스트)| 날짜 선택기 | 워크시트 병합 | 암호화/셀 해독 | 목록으로 이메일 보내기 | 슈퍼 필터 | 특수 필터(굵은 글꼴이 있는 셀 필터링/기울임꼴/취소선。。。) 。。。 | |
| 상위 15 도구 모음:12 텍스트도구(텍스트 추가,특정 문자 삭제, ...)| 50+차트유형(간트 차트, ...)| 40+ 실용적인수식(생일을 기준으로 나이 계산, ...)| 19 삽입도구(QR 코드 삽입,경로에서 그림 삽입, ...)| 12 변환도구(단어로 변환하기,환율 변환, ...)| 7 병합 및 분할도구(고급 행 병합,셀 분할, ...)|그 외 더 많은 기능 |
Kutools for Excel 로 Excel 역량을 한 단계 업그레이드하고 전례 없는 효율성을 경험하세요。Kutools for Excel 는 생산성과 저장 시간을 향상시키는 300 개 이상의 고급 기능을 제공합니다。가장 필요한 기능을 지금 바로 확인하세요。。。
Office Tab 가 Office 에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게 만들어 줍니다
- Word, Excel, PowerPoint 에서 탭 기반 편집 및 읽기 기능을 활성화합니다, Publisher, Access, Visio 및 Project 에서도 사용 가능합니다。
- 새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 생성할 수 있습니다。
- 50% 만큼 생산성을 높이고 매일 수백 번의 마우스 클릭을 줄여줍니다！
모든 Kutools 애드인。 하나의 설치 프로그램
Kutools for Office스위트 번들은 Excel, Word, Outlook 및 PowerPoint 용 애드인과 Office Tab Pro 를 포함하며， 다양한 Office 앱을 사용하는 팀에 이상적입니다。
- 올인원 스위트— Excel， Word， Outlook 및 PowerPoint 애드인 + Office Tab Pro
- 하나의 설치 프로그램， 하나의 라이선스— 몇 분 안에 설정 완료(MSI 지원)
- 함께 사용할수록 더 효과적입니다— Office 앱 전반에서 생산성 향상
- 30 일간 모든 기능 무료 체험— 등록이나 신용카드 필요 없음
- 최고의 가성비— 개별 애드인 구매 대비 절약