Concatenate and join cells in Excel and concatenate and join in VBA

4 ways to concatenate cells in Excel

You can concatenate or join two or more Excel cells using 4 different ways.

Concatenate function in Excel

In concatenate function you have to input individual cells or text separated by comma.

Cocatenate(text1,[text2],…)

In the below image cell D4 cell formula is =CONCATENATE(A4,B4,C4)

If you want to put separator or delimiter between individual values input that separator after value separated by comma.

Cell D6 formula =CONCATENATE(A6,” “,B6,” “,C6) . Here ” ” (space) is delimiter.

Cell D7 formula =CONCATENATE(A7,”-“,B7,”-“,C7) . Here “-” (dash) is delimiter.

Note:- You can not input range reference like this =CONCATENATE(A4:C4). You have to input individual cell reference like this =CONCATENATE(A4,B4,C4).

You can ipnut literal values or hard values like this =CONCATENATE(“Praskash”,”-“,”Jay”,”-“,”Patil”) will output Praskash-Jay-Patil.

To concatenate numbers no need to wrap numbers around double quotes =CONCATENATE(1,2,3) will output 123.

Any concatenate or join function always outputs a string. =CONCATENATE(1,2,3) will output 123 which is a string.

To convert into number multiply by 1 like this =CONCATENATE(1,2,3)*1 or use NumberValue function like this =NUMBERVALUE(CONCATENATE(1,2,3)) this will output 123 as number.

Tip:- Instead of putting comma manually after each cell just press hold CTRL key and select cells one by one using mouse, this will put comma automatically.

=CONCATENATE( –press hold CTRL key and select cells)

Using ampersand (&) to join cell values

In this method each cell or text is joined using & (ampersand).

Cell D4 formula =A4 & B4 & C4

For space delimiter put ” ” (space) like below

=A4 &” ” & B4 & ” ” & C4 will output Prakash Jay Patil

=”abc” &” ” & “123” will output abc 123

Concat function in Excel

Concat function is similar to concatenate and also permit users to input range reference. This function is available in Excel 2019 and onward versions.

Cocat(text1,[text2],…)

Cell D4 formula =CONCAT(A4:C4) will output PrakashJayPatil but in Concatenate you have to input individual cells like this =CONCATENATE(A4,B4,C4).

=CONCAT(A4,B4,C4) will also work and outpus PrakashJayPatil.

=CONCAT(A4:C4) style you can not input delimiter.

=CONCAT(A4,B4,C4) style you can put delimiter like this =CONCAT(A4,”-“,B4,”-“,C4) will output Prakash-Jay-Patil .

Textjoin function in Excel

Textjoin is most advance function to concatenate cells in Excel. This function is available in Excel 2019 and onward versions.

Textjoin(delimiter,ignoreEmpty,text1,text2,..)

Cell D4 formula =TEXTJOIN(” “,TRUE,A4:C4) outputs Prakash Jay Patil.

1st input of Textjoin function is delimiter, in the above formula ” “(space) is used as delimiter.

2nd input is whether to ignore empty space present in join range.

3rd input is join range or cells or text.

In the below image cell D4 formula is =TEXTJOIN(“-“,FALSE,A4:C4) outputs Prakash–Patil.

In above formula False means do not ignore space hence after space “-” (dash) is there.Prakash-(space/nostring)-Patil.

Whereas cell D5 formula is =TEXTJOIN(“-“,TRUE,A5:C5) which outputs Prakash-Patil.

In cell D5 formula ignore empty is set to True hence there is single “-” (dash) between Prakash and Patil.

Double quote around text Excel formula

To top double quotes around text cell D5 formula be written as below one

=””””&TEXTJOIN(“”””,TRUE,A5:C5)&”””” outputs “Prakash”Patil”

=””””& “Prakash” & “””” outputs “Prakash”

In the above formula 4 double quotes is used to generate one double quote.

Concatenate or join text in Excel VBA

Using & method

Literal value (hard value) join in VBA

This method is widely used for concatenating or joining strings and variables in VBA

Sub test()
 MsgBox "One plus two is " & 1 + 2
End Sub

If you run above subroutine or procedure then it will give message box like then below image. To run subroutine press cursor on any line of subroutine then press F5 function key.

You can also make variables for individual parts of a string then concatenate these variables. Instead of message box, I will use Debug.Print to see concatenated output in immediate window.

Press CTRL+G to bring immediate window then run below procedure. You can see concatenated string in immediate window.

Sub test()
    Dim str1, str2
    str1 = "One plus two is "
    str2 = 1 + 2
    Debug.Print str1 & str2
End Sub

Join or concatenate cell or range value in VBA

Sub test()
  MsgBox Range("A3").Value & " " & Range("C3").Value
End Sub

Above procedure displays Prakash Patil in message box.

Alternatively you can have variables to store range value then concatenate it. Like the below code section.

Sub test()
    Dim val1 As String, val2 As String
    val1 = Range("A3").Value
    val2 = Range("C3").Value
    
    Debug.Print val1 & " " & val2
End Sub

Using + method

Using + (plus) you can concatenate string data type only. So, for using this method data type must be of string type.

Sub testPlusMethod()
'Debug.Print prints value in immediate window
    Dim var1 As String, var2 As String
    var1 = "Excel "
    var2 = "VBA"
    
    'prints One plus two is 3
    Debug.Print "One plus two is " + "3"
    'prints Excel VBA
    Debug.Print var1 + var2
  
    Dim val1 As String, val2 As String
    val1 = Range("A3").Value
    val1 = Range("C3").Value
    'prints cell A3 and C3 value separated by a space
    Debug.Print val1 + " " + val1
    
    'prints 3
    Debug.Print 1 + 2
    'prints 3
    Debug.Print 1 + "2"
    'prints 12
    Debug.Print "1" + "2"
    
    'run time error (Type mismatch)
    Debug.Print "On plus two is " + 3
End Sub

Debug.Print 1 + 2 and Debug.Print 1 + “2” prints 3 instead of 12 this is because priority is given to perform add operation if one of the values found to be of number type.

Debug.Print “1” + “2” prints 12 because both are string.

Debug.Print “On plus two is ” + 3 will give error since you can not add number to string. To eliminate this error at place of 3 write “3”.

Displaying Double Quotes around text or string in VBA

To display double quotes around a string you need to concatenate 4 double quotes with a given string.

Below procedure will print “Ramesh” in immediate window. You can see 4 double quotes (“”””) concatenated with “Ramesh” string.

Sub test()
 Debug.Print """" & "Ramesh" & """"
End Sub

Using Join VBA function

VBA join function require two inputs and outputs string data type

1st one is array which is mandatory. This array is one dimensional array.

2nd one is delimiter which is optional

Join(SourceArray,[Delimiter]). By default delimiter is space.

Sub JoinTest()
  Dim letterArray As Variant
  letterArray = Array("a", "b", "c", "d")
  'prints space separated a b c d
  Debug.Print Join(letterArray)
  'prints abcd
  Debug.Print Join(letterArray, "")
  'prints a,b,c,d
  Debug.Print Join(letterArray, ",")
End Sub

But how you can use this Join function to concatenate values in Range.

In the below image I want to concatenate First name, Middle Name and Last Name using Join function. Also want to join or concatenate Random text using Join.

In Excel VBA range variable is always two or more dimensional array represented by nth row and nth column.

The trick here is to convert range into one dimensional array. So that join function be used.

Join or concatenate Range values using Join VBA function

Transpose function is used to convert range into one dimensional array.

Also note that variable holding these one dimensional array must be variant type.

If range has only one column or data is in a column range then use below syntax to convert into one dimensional array.

Application.Transpose(Range(“ref”).Value)

If range has only one row or data is in a row range then use below syntax to convert into one dimensional array.

Application.Transpose(Application.Transpose(Range(“ref”).Value))

Sub JoinRangeValues()
 Dim rwRngAr As Variant, clmRngAr As Variant
 
 ''when data is placed in single ROW range
 rwRngAr = Application.Transpose(Application.Transpose(Range("A3:C3").Value))
 'prints Prakash,Jay,Patil
 Debug.Print Join(rwRngAr, ",")
 
 ''when data is placed in single COLUMN range
 clmRngAr = Application.Transpose(Range("E3:E8").Value)
 'prints a-b-45-L-M-1
 Debug.Print Join(clmRngAr, "-")
End Sub

Making an UDF (User Defined Function) as an alternate of Textjoin function for all Excel versions

Excel Textjoin function is the most advance concatenate function but only avaliable in Excel 2019 and onward version.

I am going to show you how you can make your own Textjoin like custom function (UDF) in Excel.

Step 1st – Press ALT+F11 to open VBA IDE . Add new standard module by clicking on Insert tab then click on module.

Step 2nd – Double click on newly created standard module and copy paste the below code in that module. Make sure to save you workbook either in .xlsb format or .xlsm format. You can see UDF function name is my_TextJoin.

Option Explicit

Function my_TextJoin(joinRng As Range, _
ignoreEmpty As Boolean, Optional deli As String = "") As String

    Dim joinRngArr As Variant, newArr() As Variant, val As Variant, i As Long
    Dim eleCnt As Long, nonBlnkCnt As Long
    
    If joinRng.Rows.Count = 1 Then
       joinRngArr = Application.Transpose(Application.Transpose(joinRng.Value))
    ElseIf joinRng.Columns.Count = 1 Then
       joinRngArr = Application.Transpose(joinRng.Value)
    Else
       my_TextJoin = ""
    Exit Function
    End If
    
    eleCnt = UBound(joinRngArr)
    nonBlnkCnt = Application.WorksheetFunction.CountA(joinRng)
    If ignoreEmpty Then eleCnt = nonBlnkCnt
    ReDim Preserve newArr(1 To eleCnt)
    i = 1
 
   For Each val In joinRngArr
       If val = vbNullString And ignoreEmpty Then GoTo jmp
       newArr(i) = val
       i = i + 1
jmp:
   Next val
    
    my_TextJoin = Join(newArr, deli)
End Function

After doing the above steps you can use my_TextJoin function in Excel cell.

1st input is join range, 2nd is ignore empty or not , 3rd is delimiter. 3rd input is optional and default delimiter is null string (“”).

Cell D3 formula =my_TextJoin(A3:C3,TRUE,”-“)

Cell H3 formula =my_TextJoin($F$3:$F$8,TRUE,”-“)

Cell H6 formula =my_TextJoin($F$3:$F$8,FALSE,”-“)