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 SubIf 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 SubAbove 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 SubUsing + 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 SubDebug.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 SubUsing 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 SubBut 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 SubMaking 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 FunctionAfter 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,”-“)
You may also like
Return multiple Vlookup values in one cell (Text join if) 3 ways
How to Join or Concatenate in Excel VBA
https://www.youtube.com/embed/2wxLOK-Q9Gk