Joins multiple text strings into one text string. Modern replacement for CONCATENATE, supporting ranges and arrays. Essential for text combination and dynamic content creation in Excel.
Master the fundamentals of Excel CONCAT function
The CONCAT function is Excel's modern text joining tool that combines multiple text strings into one string. It's an improved version of CONCATENATE, supporting ranges and arrays, making it essential for text combination and dynamic content creation.
Can join entire ranges directly
Supports array constants and formulas
Replacement for CONCATENATE
Includes empty strings from empty cells
Function-specific parameters
Function-specific return type
Combine text from multiple sources
Join entire ranges without individual references
Create dynamic text content from data
Format and combine data for reports
Exact matching required
Returns numeric position
Handles missing text gracefully
=CONCAT(text1, text2)First text string to join
Additional text strings or ranges to join
Combined text string
Description: Joins multiple text strings into one text string. Supports ranges and arrays, unlike CONCATENATE.
Join two text strings
Joins three text strings with a comma and space
Simple VBA implementation of CONCAT function
' Basic CONCAT in VBA
Range("C1").Value = Application.WorksheetFunction.Concat(Range("A1").Value, " ", Range("B1").Value)
' Using VBA CONCAT function
Dim result As String
result = Application.WorksheetFunction.Concat("Hello", " ", "World")
' Concatenate range values
Sub ConcatRange()
Dim cell As Range
Dim result As String
result = ""
For Each cell In Range("A1:A10")
If Not IsEmpty(cell.Value) Then
result = result & cell.Value
End If
Next cell
Range("B1").Value = result
End Sub
' Advanced concatenation with delimiter
Sub ConcatWithDelimiter()
Dim cell As Range
Dim result As String
Dim firstCell As Boolean
firstCell = True
result = ""
For Each cell In Range("A1:A10")
If Not IsEmpty(cell.Value) Then
If Not firstCell Then
result = result & ", "
End If
result = result & cell.Value
firstCell = False
End If
Next cell
Range("B1").Value = result
End SubAdvanced VBA function to mimic CONCAT range behavior
' Function to concatenate range (like CONCAT)
Function ConcatRange(rng As Range) As String
Dim cell As Range
Dim result As String
result = ""
For Each cell In rng
If Not IsEmpty(cell.Value) Then
result = result & CStr(cell.Value)
End If
Next cell
ConcatRange = result
End Function
' Usage in worksheet
' =ConcatRange(A1:A10)
' Concatenate with custom delimiter
Function ConcatRangeDelimited(rng As Range, delimiter As String) As String
Dim cell As Range
Dim result As String
Dim firstCell As Boolean
firstCell = True
result = ""
For Each cell In rng
If Not IsEmpty(cell.Value) Then
If Not firstCell Then
result = result & delimiter
End If
result = result & CStr(cell.Value)
firstCell = False
End If
Next cell
ConcatRangeDelimited = result
End FunctionCombine text from multiple cells
Join entire ranges without individual references
Create dynamic text from data
Format and combine data for reports
CONCAT includes empty strings from empty cells in ranges
=CONCAT(IF(A1:A10<>"", A1:A10, ""))Solution: Use FILTER or IF to exclude empty cells: =CONCAT(IF(A1:A10<>"", A1:A10, ""))
Numbers in ranges are concatenated as text
=CONCAT(TEXT(A1, "0"))Solution: This is expected. Use TEXT function to format: =CONCAT(TEXT(A1, "0"))
CONCAT may be slow with very large ranges
=CONCAT(A1:A1000)Solution: Consider breaking into smaller ranges or using VBA for better performance with large datasets.
CONCAT doesn't have built-in delimiter parameter
=TEXTJOIN(", ", TRUE, A1:A10)Solution: Use TEXTJOIN for delimiter support, or combine with IF logic: =CONCAT(A1, IF(A2<>"", ", " & A2, ""))