Combine functions to insert a character inside a text string

FORMULAS

Combine functions to insert a character inside a text string

Using nested functions (combine functions) in Excel formula is very powerful technique which can save you hours of your precious time.

Few days ago, I had a chance to help one of my colleagues to resolve one formula issue. The invoice number needed to be created based on existing text string with adding one blank character inside the string. For example, using text string “AB0001” we should generate invoice number “AB 0001”. There was a big range of data waiting to be edited in this way, so that final invoice numbers could be used further in the data analysis.

Definitely, this could be done manually, but when you have a great amount of data this manual work is killing your motivation and your efficiency drops dramatically. Not to mention probability of making a mistake, which will in the end take you even more time to fix.

Great thing with using Excel functions is that you can combine them and use their individual results in one common formula to get final result you need. The most important thing when you combine functions is that you know the optimal function combination.

The best way to find out which functions are the best for your task is that you make the analysis of the steps.

Let’s present this technique using our example from above. There are a few different methods and formulas you could use for this kind of task, depending on how much your data are standardized. In our case we had two letter characters followed by four number characters, and we should add blank character after second letter character from the left. This was the same request for each of our invoice numbers. Knowing this, we decided it is the most optimal solution for us to use further functions: CONCATENATE, LEFT and RIGHT.

The idea was to use CONCATENATE as main function. Inside of it we would use LEFT function to select letter characters as first element of concatenation, then we would add blank character as second element and finally RIGHT function to select number characters as third element of concatenation. We tried it and it was like a magic. The formula we created looked just like this:

=CONCATENATE(LEFT(A2;2);” “;RIGHT(A2;4))

Data set edited with this formula would create the result like on the further picture.

But what if you have the strings with different number of characters?

If you have the strings with different number of characters in your range of data this is little more complicated but don’t be worry, just read further. We will add one more function in the formula and it will be done in the blink of an eye, too. We will enhance existing formula to adjust string length variations.

So, if text strings in your data set are not quite standardized here you can find out how to use LEN function to provide data for RIGHT function and insert a character inside a text string.