Concatenation Operators

Concatenation Operator

The ampersand (&) operator is used to concatenate or join strings together in VBA. This is particularly useful when you want to combine text, numbers, or cell values into a single string.

Syntax:

Purpose: Returns True only if both conditions are True.

result = string1 & string2 & … & stringN

				
					Sub ConcatenateStrings()
    Dim firstName As String
    Dim lastName As String
    Dim fullName As String

    firstName = "John"
    lastName = "Doe"

    fullName = firstName & " " & lastName 1 

    MsgBox fullName
End Sub

Example

				
					Sub ConcatenateCellValues()
    Dim cellValue1 As String
    Dim cellValue2 As String
    Dim combinedValue As String

    cellValue1 = Range("A1").Value
    cellValue2 = Range("B1").Value

    combinedValue = cellValue1 & " " & cellValue2

    Range("C1").Value = combinedValue
End Sub

Example: Concatenating with Cell Values

				
					Sub ConcatenateNumbersAndText()
    Dim number As Integer
    Dim text As String
    Dim combinedText As String

    number = 10
    text = "apples"

    combinedText = "You have " & number & " " & text

    MsgBox combinedText
End Sub

Example: Concatenating Numbers and Text

Key Points:

The & operator can be used to concatenate strings, numbers, and cell values.
You can use multiple & operators to concatenate multiple strings.
To add spaces between concatenated elements, include spaces within the quotation marks or use the & " " syntax.
When concatenating numbers and text, ensure that the numbers are converted to strings using the Str function or by directly concatenating them with a string.