The concatenate formula is one of those formulas that I would use almost on a daily basis, so I thought I would share with you an example of how I’ve used it recently 🙂

For those of you not familiar with this formula, it’s an easy way to join several cells of text into one cell of text, as you’ll see in this posts example.

The other day a client asked me if there was an easy way to amend a list of mobile phone numbers which had been entered as 08 followed by the rest of the number.  She actually wanted them to appear with the Ireland country code, so they would start with +353 followed by the number.  She had a list of over 1000 numbers so manually going into each one & changing it was out of the question!

Below is a guide showing you how to use the concatenate formula in order to change the mobile number list.

You’ll notice the list of mobile numbers don’t have a 0 at the beginning – this is because they’ve been automatically formatted as a number (& not text), so the “0” was not visible (the example shows the mobile number for OutofhoursAdmin ;))…

I wanted to create a formula that would insert +353 to the beginning of each number.  Firstly, make sure you’ve clicked on the cell where you want the answer to appear.  Then the best place to start, if you’re not familiar with using different formulas, is on the Formulas tab in the ribbon and choose which category the formula you want would fall under. For this example it would be Text, click on the arrow beneath Text and a list of text functions will appear…

Click on Concatenate and it opens up the function argument…

Next we need to enter the 1st text string.  If the text was in another cell somewhere within the worksheet, then we would click on that cell and it would automatically appear in the Text1 box.  For this example I want the first text string to be +353, so type in “+353” into the Text1 box…

Text2 will be the mobile number from the list, so just click on the cell which contains the first mobile number…

You can see the actual formula result as you type the individual text strings, so you can keep an eye on whether it’s correct without having to come out of it.  Click OK, and the cell will now contain the mobile number preceded by +353…

Then, just copy the formula down the rest of the column – a quick way of doing this is to double click the drag fill handle in the bottom right corner of the cell and let autofill do its job…

And that’s one way to use the concatenate formula – I hope you find it just as useful as I do 🙂