tech on the net

MS Excel: Concatenate with & (WS, VBA)

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]

string1 to string_n are the string values to concatenate together.

Applies To

Type of Function

Worksheet Function Example

Let's take a look at an example to see how you would use the "&" operator in a worksheet:

Microsoft Excel

Based on the Excel spreadsheet above, the "&" operator would return the following:

=A1&A2 would return "Alphabet"
="Tech on the "&"Net" would return "Tech on the Net"
=(A1&"bet soup") would return "Alphabet soup"

VBA Function Example

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"))