Purpose
How to concatenate in Excel, join text and/or numbers together.
The values can be entered directly into the function, or pulled in from cell contents by using cell references. The result is a text string.
Links to topics in this page
- Syntax example of CONCATENATE function
- Example: create a SUM CASE WHEN statement for SQL in Excel
- How to create a set of SUM (CASE WHEN …) statements for SQL in Excel using the CONCATENATE function
- How to include a single quote at the start of a cell in Excel
Syntax example — join the contents of cells B3 and C3 with text strings.
= CONCATENATE(“The summary for “,B3,” was “, C3)
Result: “The summary for November was £3,500”
[where B3 contains ‘November’, C3 contains ‘£3,500’]
Example – create a SUM CASE WHEN series of statements for SQL
The structure for a SELECT SUM(CASE WHEN…) statement in SQL is:
SUM(CASE WHEN [variable] [condition] THEN [result1] ELSE [result2] END)
,SUM (CASE WHEN CUST_ID = ‘100’ THEN ORDER_QUAN ELSE 0 END) as ‘100’
The above example finds the order quantity (ORDER_QUAN) for customer ID 100 (CUST_ID) and labels the row/record in the resulting table as the customer ID 100.
You can use Excel’s CONCATENATE/CONCAT function to generate a list of SQL statements, as shown below.
Method (see video above)
The method uses the newer version of CONCATENATE( ) which is CONCAT( ), that is gradually being phased in for users of Office 365, and Windows and Mac users of Office 2019.
- Enter the separate chunks of text and values / Cell references into cells.
- Use the CONCAT function to combine the range of cells to create the desired text string.
- Fill the cells down the rows as required.
Note: how to include a single quote at the start of a cell in Excel
Because Excel interprets a single quote [‘] at the start of a cell in a particular way (i.e. it turns the cell contents after the [‘] to text, but doesn’t include the [‘] in the actual text string), you need to add an extra single quote.
- To include a single quote [‘] at the start of a cell there are two options (as shown in the video above):
- add an extra single quote [‘ ‘], or
- change the cell format to ‘Custom: ‘@‘ — click on the right-hand corner of the cell format section of the ribbon, and in the ‘Format Cells’ dialog box, choose ‘Custom’, and enter ‘@ [single quote followed by ampersand].
This is useful in some circumstances. For example, to enter a formula in a cell and prevent Excel from running it (and potentially throwing up errors) while you’re typing it in, you enter a [‘] at the start of the cell which denotes the contents as text, and not a formula or function to run. When you are ready to run the formula or function, you can delete the [‘] at the start of the cell.