Excel에서 텍스트와 함께 또는 텍스트 내에 증가하는 번호를 생성하는 방법은 무엇인가요?

Excel에서 순차적으로 증가하는 번호 시퀀스를 생성하는 작업은 데이터 정렬이나 고유 식별자 생성을 위해 필수적입니다. AutoFill 핸들 기능을 사용하면 연속된 독립 숫자를 쉽게 채울 수 있지만, "User001", "User002"처럼 텍스트 문자열과 증가 번호를 한 번에 결합하는 기능은 지원하지 않습니다. 이러한 제한으로 인해 사용자명, 이메일 주소, 제품 코드, 송장 번호 등과 같이 고정 텍스트와 순차 번호가 모두 필요한 작업이 복잡해질 수 있습니다. 이 튜토리얼에서는 기본 수식부터 VBA 자동화, 그리고 사용하기 쉬운 전문 도구에 이르기까지, Excel에서 텍스트와 통합하거나 텍스트 내부에 번호가 포함된 증가 번호를 생성할 수 있는 여러 방법을 소개합니다.

일반적인 활용 사례로는 고유 라벨이 붙은 항목 배치 생성, 체계적인 네이밍의 이메일 목록 만들기, 제품 추적을 위한 일련번호 생성 등이 있습니다. 각 방법은 사용 편의성, 자동화, 번호 위치 유연성, 대용량 데이터 처리 등 각각의 장점이 있습니다.

Excel 수식으로 텍스트와 함께 증가 번호 만들기
Kutools for Excel로 텍스트와 함께 증가 번호 만들기
VBA 매크로: 텍스트에 포함하거나 내부에 증가 번호 생성

Excel 수식으로 텍스트와 함께 증가 번호 만들기

Excel의 기본 내장 수식은 텍스트와 증가하는 번호를 결합하는 간단한 방법을 제공합니다. 번호와 텍스트의 위치가 고정되어 있고, 식별자 구성이 명확히 드러나길 원하는 경우 특히 유용합니다.

1. 아래와 같이 시트를 준비하세요:

2. D2 셀에 다음 수식을 입력한 후, Enter를 눌러 적용하세요:

=$A$2&TEXT(B2,"000")&$C$2

수식이 결과를 반환하면, D2 셀의 채우기 핸들을 아래로 드래그하여 D열의 나머지 셀까지 복사하세요. 각 셀에는 “User001@localdomain.com”, “User002@localdomain.com” 등과 같이 텍스트와 증가 번호가 결합된 값이 표시됩니다. 아래 예시와 같습니다:

참고 및 팁:

이 방법은 데이터 구조가 잘 갖춰진 경우 투명성이 높아 이상적이지만, 각 구성요소마다 보조 열을 수동으로 설정해야 하는 번거로움이 있습니다. 또한 텍스트와 번호의 위치가 행마다 다르면 추가 수식 수정이 필요할 수도 있습니다.

Kutools for Excel로 텍스트와 함께 증가 번호 만들기

증가하는 텍스트 식별자를 자주 생성해야 하거나 시각적 인터페이스 및 동적인 옵션(유연한 접두사, 접미사, 사용자 지정 증분, 반복 사용을 위한 옵션 저장 등)이 필요하다면 Kutools for Excel의 시퀀스 번호 삽입 기능을 활용해보세요. 이 도구를 사용하면 단 몇 번의 클릭으로 복잡한 번호 체계를 만들 수 있으며, 별도의 수식 설정이 필요 없습니다.

1. Excel 리본에서 Kutools > Insert > Insert Sequence Number…로 이동하세요.

2. 시퀀스 번호 삽입 대화상자에서 새로 만들기를 클릭하면 추가 옵션이 확장됩니다. 설정 방법은 다음과 같습니다:

3. 증가 번호와 텍스트를 삽입할 범위를 선택한 뒤, 범위 채우기(Fill Range)를 클릭하면, 맞춤형 번호가 선택한 영역에 즉시 채워집니다. 예: “User001@localdomain.com”, “User002@localdomain.com”…

추가 안내: Kutools에서 번호 매기기 템플릿을 만들고 저장하면, 여러 워크북에서도 빠르게 재사용할 수 있습니다. 마지막으로 생성된 번호가30이었다면, 다음 시퀀스 실행 시 자동으로31부터 번호가 이어집니다. 이는 중복 번호 방지나 대규모 데이터셋 추적에 특히 유용합니다.

이 방법은 일상적으로 순차 데이터 작업을 하며 클릭 한 번으로 효율성과 유연성을 모두 원하거나, 반복 배치 작업용 템플릿 저장이 필요한 사용자에게 추천됩니다. 접두사/접미사 이상의 위치 기반 고급 커스터마이징이 필요할 경우 아래 자동화 방식이 더 적합할 수 있습니다.

Kutools for Excel로 텍스트와 함께 증가 번호 만들기

VBA 매크로: 텍스트에 포함하거나 내부에 증가 번호 생성

번호를 텍스트 내 어느 위치에도 삽입하거나, 즉석에서 복잡한 식별자를 생성하거나, 여러 행에 한 번에 적용해야 하는 등 최대한의 제어가 필요하다면 VBA 매크로를 사용하는 것이 가장 적합합니다. VBA는 대량 작업, 독특한 패턴 지정, 텍스트의 시작이나 끝이 아닌 어느 곳에나 번호를 삽입해야 할 때 뛰어난 자동화와 커스터마이징을 제공합니다.

작업을 시작하기 전에, VBA 코드를 잃지 않으려면 반드시 통합 문서를 매크로 사용 가능 파일로(.xlsm) 저장하세요. 또한 데이터 복사본에서 먼저 매크로를 테스트해 예기치 않은 변경을 방지하는 것이 좋습니다.

매크로 사용 방법:

1. 워크북을 연 다음, Excel 리본에서 개발 도구 > Visual Basic을 클릭하세요. (개발자 탭이 보이지 않으면 파일 > 옵션 > 리본 사용자 지정에서 활성화하세요.)

2. Microsoft Visual Basic for Applications 편집기 창에서 Insert > Module을 클릭하세요. 아래 코드를 복사해 빈 모듈에 붙여넣으세요:

Sub InsertIncrementNumberIntoText()
    Dim cell As Range
    Dim rng As Range
    Dim startNum As Long
    Dim increment As Long
    Dim pattern As String
    Dim position As Variant
    Dim currentNum As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select the range to apply incremented text.", xTitleId, rng.Address, Type:=8)
    
    startNum = Application.InputBox("Enter the starting number:", xTitleId, 1, Type:=1)
    
    increment = Application.InputBox("Enter the increment between numbers:", xTitleId, 1, Type:=1)
    
    pattern = Application.InputBox("Enter the pattern (use {n} to mark the insertion point, e.g. 'User{n}@local.com'):", xTitleId, "User{n}@local.com", Type:=2)
    
    currentNum = startNum
    
    If rng Is Nothing Then Exit Sub
    
    For Each cell In rng
        cell.Value = Replace(pattern, "{n}", currentNum)
        currentNum = currentNum + increment
    Next
End Sub

3. 매크로 실행을 위해 버튼을 클릭하거나 F5을(를) 누르세요. 대화창이 표시되어 대상 범위 선택, 시작 번호, 증분, 원하는 텍스트 패턴(예: ID-{n}-LIST 와 같이 사용자 지정 텍스트 사이에 증가 번호를 삽입하는 경우) 등을 설정할 수 있습니다.

• 패턴 내 어디에든 "{n}"을 삽입할 수 있으며, 이를 통해 증가 번호의 위치를 자유롭게 지정할 수 있습니다.
• 확인하면, 선택한 각 셀이 선택한 패턴 및 간격에 따라 올바르게 증가된 식별자로 채워집니다.
• 다른 영역에 매크로를 적용하려면 다시 실행한 뒤 원하는 매개변수를 지정하면 됩니다.
• 실수했다면 실행 취소(Ctrl+Z) 기능을 사용하거나 매크로를 다시 실행하세요.

문제 해결 및 팁:

이 솔루션은 파일명 내부에 번호 삽입, 여러 행에 동시에 패턴화된 ID 생성, 복잡한 순차 네이밍 자동화 등 고급 사용 사례에 특히 유용합니다.