This Excel tutorial explains how to use the Excel & operator with syntax and examples.
Based on the spreadsheet above, the following Excel "&" operator examples would return:
The "&" operator can be used to concatenate strings in VBA code. For example:
For example, I want to put an equation for work hours and pay. If I am paid more than I should be, I want it to read how many hours I owe my boss. But if I work more than I am paid for, I want it to read what my boss owes me (hours*Pay per Hour).
I tried the following:
Answer: There are two ways that you can concatenate text and values. The first is by using the & character to concatenate:
Description
To concatenate multiple strings into a single string in Microsoft Excel, you can use the "&" operator to separate the string values.Syntax
The syntax for the "&" operator is:string1 & string2 [& string3 & string_n]
Parameters or Arguments
string1 to string_n are the string values to concatenate together.Applies To
The "&" operator can be used in the following versions of Microsoft Excel:- Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Type of Excel Function
The "&" operator can be used in Microsoft Excel as the following type of function:- Worksheet function (WS)
- VBA function (VBA)
Example (as Worksheet Function)
Let's look at some Excel "&" operator examples and explore hwo you would use the "&" operator as a worksheet function in Microsoft Excel:Based on the spreadsheet above, the following Excel "&" operator examples would return:
=A1&A2 Result: "Alphabet" ="Tech on the "&"Net" Result: "Tech on the Net" =(A1&"bet soup") Result: "Alphabet soup"
Example (as VBA Function)
Let's look at some Excel "&" operator function examples and explore how to use the "&" operator in Excel VBA code:The "&" operator can be used to concatenate strings in VBA code. For example:
Dim LValue As String LValue = "Alpha" & "bet"The variable LValue would now contain the value "Alphabet".
Frequently Asked Questions
Question:For an IF statement in Excel, I want to combine text and a value.For example, I want to put an equation for work hours and pay. If I am paid more than I should be, I want it to read how many hours I owe my boss. But if I work more than I am paid for, I want it to read what my boss owes me (hours*Pay per Hour).
I tried the following:
=IF(A2<0,"I owe boss" abs(A2) "Hours","Boss owes me" abs(A2)*15 "dollars")Is it possible or do I have to do it in 2 separate cells? (one for text and one for the value)
Answer: There are two ways that you can concatenate text and values. The first is by using the & character to concatenate:
=IF(A2<0,"I owe boss " & ABS(A2) & " Hours","Boss owes me " & ABS(A2)*15 & " dollars")Or the second method is to use the CONCATENATE function:
=IF(A2<0,CONCATENATE("I owe boss ", ABS(A2)," Hours"), CONCATENATE("Boss owes me ", ABS(A2)*15, " dollars"))
