Easy Excel | EXCEL 2016 이하 CONCAT/TEXTJOIN 구현

함수 설명

Excel 2019부터 여러 영역의 텍스트를 손쉽게 합칠 수 있는 CONCAT 함수와, 구분자를 지정하여 텍스트를 연결하는 TEXTJOIN 함수가 내장되어 있습니다. 하지만 Excel 2016 이하 버전에서는 이러한 함수를 사용할 수 없습니다.

기존의 CONCATENATE 함수는 텍스트를 합치기 위해 각 셀을 하나씩 개별적으로 지정해야 하는 (=CONCATENATE(A1, A2, A3, ...) ) 불편함이 있습니다.

이러한 한계를 극복하기 위해, CONCAT과 TEXTJOIN의 기능을 모두 수행하는 사용자 정의 함수를 만들어 사용할 수 있습니다. 이 함수를 사용하면 여러 영역을 한 번에 지정하고, 필요에 따라 텍스트 사이에 구분자를 넣어 손쉽게 병합할 수 있습니다.

코드

사용자 함수를 추가 및 사용하는 방법은 VBA/사용자 함수 소개글을 확인해주세요.

Function CONCAT_UDF(ParamArray args() As Variant) As String
    ' 오류 발생 시 #VALUE! 오류를 반환합니다.
    On Error GoTo ErrorHandler

    Dim delimiter As String
    Dim resultArr() As String
    Dim i As Long
    Dim startIdx As Long
    Dim rng As Range
    Dim cell As Range
    Dim count As Long

    ' 동적 배열 및 변수 초기화
    ReDim resultArr(0 To -1)
    count = 0
    delimiter = ""
    startIdx = LBound(args)

    ' 첫 번째 인수가 Range 개체가 아니면 구분자로 간주합니다.
    If Not IsObject(args(LBound(args))) Then
        delimiter = CStr(args(LBound(args)))
        startIdx = LBound(args) + 1 ' 실제 데이터는 두 번째 인수부터 시작합니다.
    End If

    ' 지정된 각 인수를 순회합니다.
    For i = startIdx To UBound(args)
        ' 인수가 Range 개체인 경우, 각 셀을 순회합니다.
        If TypeName(args(i)) = "Range" Then
            Set rng = args(i)
            For Each cell In rng.Cells
                ' 셀이 비어있지 않은 경우에만 값을 배열에 추가합니다.
                If Not IsEmpty(cell.Value) And cell.Value <> "" Then
                    ReDim Preserve resultArr(0 To count)
                    resultArr(count) = CStr(cell.Value)
                    count = count + 1
                End If
            Next cell
        ' 인수가 Range가 아닌 일반 값인 경우
        Else
            ' 값이 비어있지 않은 경우 배열에 추가합니다.
            If Not IsEmpty(args(i)) And args(i) <> "" Then
                ReDim Preserve resultArr(0 To count)
                resultArr(count) = CStr(args(i))
                count = count + 1
            End If
        End If
    Next i

    ' 배열의 모든 요소를 구분자로 연결하여 최종 문자열을 만듭니다.
    CONCAT_UDF = Join(resultArr, delimiter)
    Exit Function

ErrorHandler:
    CONCAT_UDF = CVErr(xlErrValue)
End Function

함수 설명 및 예제

함수 사용법

CONCAT_UDF 함수는 선택적 구분자와 하나 이상의 영역을 인수로 받습니다.

=CONCAT_UDF([구분자], 영역1, [영역2], ...)

[구분자] (선택 사항): 합칠 텍스트 사이에 넣을 텍스트입니다. 생략하면 텍스트들이 그대로 이어 붙습니다.
영역1 (필수 사항): 합칠 텍스트가 포함된 첫 번째 셀 영역입니다.
[영역2] (선택 사항): 합칠 텍스트가 포함된 추가적인 셀 영역입니다.

예제 데이터

◢	A	B	C
1	Easy	Excel	입니다
2		VBA
3	함수	마법사	입니다

사용 예시

1. 구분자 없이 여러 텍스트 합치기 (CONCAT 처럼 사용)

아래 수식은 A1:C1 영역의 모든 텍스트를 구분자 없이 연결합니다.

=CONCAT_UDF(A1:C1)

결과: EasyExcel입니다

2. 특정 구분자로 여러 영역 텍스트 합치기 (TEXTJOIN 처럼 사용)

아래 수식은 A1:C3 영역의 모든 텍스트를 공백(" ")을 구분자로 하여 연결합니다. 이때 빈 셀(A2, C2)은 무시됩니다.

=CONCAT_UDF(" ", A1:C3)

결과: Easy Excel 입니다 VBA 함수 마법사 입니다

3. 여러 영역을 하이픈(-)으로 연결하기

아래 수식은 A1:A3 영역과 C1:C3 영역의 텍스트를 하이픈(-)으로 연결합니다.

=CONCAT_UDF("-", A1:A3, C1:C3)

결과: Easy-함수-입니다-입니다

4. 영역과 텍스트 값을 함께 사용하기

아래 수식은 A1:C1 영역의 텍스트와 "그리고"라는 텍스트, 그리고 B3 셀의 텍스트를 공백(" ")으로 연결합니다.

=CONCAT_UDF(" ", A1:C1, "그리고", B3)

결과: Easy Excel 입니다 그리고 마법사

기타 고려사항

이 함수는 여러 개의 인수를 유연하게 처리하기 위해 VBA의 ParamArray 기능을 사용합니다. 이를 통해 함수에 영역1, 영역2, 영역3 등 원하는 만큼 영역을 전달할 수 있습니다.
함수의 첫 번째 인수가 텍스트(예: " ", "-")이면 구분자로 인식하고, 셀 영역(Range)이면 다른 영역과 합칠 데이터로 인식합니다.
TEXTJOIN 함수와 마찬가지로, 이 함수는 비어있는 셀을 무시하고 텍스트를 연결합니다.
숫자나 날짜 데이터가 영역에 포함된 경우, 해당 값은 텍스트로 변환되어 결과에 포함됩니다.