If you want to automate journal descriptions or create sentences the TEXTJOIN function is your friend. It can combine words and insert the spaces for you.
In the image below we have a basic profit calculation, and we want to automate the description of the result.
Cell B8 – The IF function determines if it is a profit or a loss.
Cell B10 – The TEXT function converts dates into text. If you don’t use TEXT and just use the date the underlying number is displayed – not what you want.
Cell B12 – The DOLLAR function converts numbers into currency. Again, without using DOLLAR the number is shown with no formatting.
Cell B14 – the TEXTJOIN function combines all the text from the range above and inserts a space between each entry. The formula for cell cell B17 is shown below.
=TEXTJOIN(" ",1,B7:B12)&"."The &”.” On the end adds the full stop to the sentence.
Leading and trailing spaces
The text entries in the range B7 to B12 do not have any leading or trailing spaces. If they did then you can remove them using the TRIM function as shown in the formula in cell B17.
I have added leading and training spaces to cells B7 and B9. The formula in cell B14 is affected but the B17 is not – see image below. The formula for cell B17 is shown below.
=TEXTJOIN(” “,1,TRIM(B7:B12))&”.”
Just having some fun here😁 while at the same time showing your readers how the TEXT function can work with arrays – this single formula will dynamically output your desired sentence…
=CONCAT(TEXT(HSTACK(B2,B1,B4),{“[>=0]””The profit””;””The loss”””,””” for “”mmmm-yyyy “,”””was “”$#,#0.”}))
Wow! Rick you turn up the TEXT function to 11.