CONCATENATE() or &
CONCATENATE() joins two pieces of text together to create a longer piece of text. You can also include the result of a calculation. Suppose in Cell M25 you have calculated profit to be 12,345. A heading can be created using concatenate: =CONCATENATE(“Profit for the Period is “,M25). This creates the text string ‘Profit for the Period is 12,345’. NB the space before the closing quote marks creates the space before the number.
The & symbol is the short form of the CONCATENATE formula. The example above could have been written =“Profit for the Period is “&M25 which would have produced exactly the same result.
TRIM()
TRIM() is another useful formula for tidying up data. It take out all spaces from the front and en
d of text and reduces spaces between ‘words’ to one. This is useful as in a lot of cases system downloads produce extra spaces particularly at the front where spacing is used to provide an indication of the level with an organisational structure. For example, in a global company the structure may go from Worldwide, Continent, Country, State (or county or department) and city. In the download an extra 3 spaces are added as we go down a level to give an appearance as in the example above . I once came across an example where there were 47 spaces before the first real character (very few people would be able to count this accurately) and for some reason unknown to me anything between 12 and 20 spaces at the end.
In the example on the right the data is in column M and TRIM() has been used to take out the spaces.
Excel Monkey 