Excel Formula Help – using CONCATENATE text and managing dates
In Microsoft Excel, the CONCATENATE function allows us to join together text entries from different cells. The function can also be used both to add text and in combination with other sub-functions.
We begin with this example set of sales leads. Selecting a cell under the table, we build our formula with the CONCATENATE function. The function pulls the contents of separate cells together but requires us to provide the space between those contents which we do by using quotation marks with a space between: =CONCATENATE(B4," ",C4)
On pressing return we see the how the function works:
However, if we try to add cell A4 to the formula to include the date, Excel delivers that date as a sequential number (the number of days since 01/01/1900) as per its default.
We can change that default behaviour by adding a sub-function. Use =TEXT(A4,"mmmm dd") after CONCATENATE and when doing so, drop the leading = from TEXT and leave a space after dd. The amended function should read =CONCATENATE(TEXT(A4,"mmmm dd ")B4," ",C4)
Hit return and we see some progress,
We are now ready to add other text elements to the final formula using quotation marks and spacing as appropriate; we include "On " with action word "called " and a final period "." as can be seen in the example here:
Care must be taken to ensure that each element of the formula is followed by a comma and that the spacing translates correctly to the final outcome, which, when copied down should look like this:
For more on how to work with text and get the most out your spreadsheet, contact our experts. More details on CONCATENATE can be found on the Microsoft website here.
