The CONCATENATE function is used in Excel to merge several strings into one.
Let’s suppose that you want to reverse the process and change the “JohnDoe45” to “John”, “Doe” and “45”.
Text to Columns
In the following example, we will try to split the text into three parts, first in cell A1, second in cell B1 and third in cell C1.
Create a copy of the string, before you use this technique. The first word will be in cell A1 and not B1, so it will overwrite the string.
Click cell A1 and go to DATA >> Data Tools >> Text to Columns.
Usually, we choose Delimited, to split characters by some kind of separators, such as space, tab or comma. In our example, we don’t have any separator between words so we will use second options, which is Fixed Width.
Here, you can set how you want to split the words. Click Finish to accept.
Another way to split text is by using the following functions: LEFT, MID, RIGHT. Choose Ctrl + ` to see the formulas.
Use the shortcut one more time it will give the following effect.
- =LEFT(A1,4) – take first four elements from cell A1.
- =MID(A1,5,3) – take three elements from cell A1 starting from the fifth element.
- =RIGHT(A1,2) – take the last two elements from cell A1.
You can create the VBA code that will split all the whole string at once.
Sub SplitText() ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=MID(RC[-2],5,3)" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=RIGHT(RC[-3],2)" End Sub
2. Let’s move the active cell one position to the right because it will be the place where we enter the first part of the string.
3. This part of the code will insert the following formula =LEFT(A1, 4). RC[-1] means that it will use the text from the cell to the left (A1) of the active cell (B1).
4-7. Here, the code inserts functions: MID and RIGHT, and move the cells accordingly.