For many purposes, while using excel, you might want to split text string at a specific character to have the wanted value only. This tutorial will help you learn how to split text with several common functions.
Split Text String At Specific Character
There are several functions in Excel to help you split text. Depends on your request, the function might be used individual or combination.
=LEFT (text, [num_chars])
Split text from the left of a string
Split text from the right of a string
Split text in the middle of a string
- text – The text from which to extract characters.
- num_chars – [optional] The number of characters to extract, starting on the left side of the text. Default = 1.
Figure 1. Example of LEFT Function
We can split text string by using LEFT function as the above example.
Basically, we will split text in reference cell A2, and take the first 4 letters on the cell. As the result, we have the User ID.
Figure 2. Example of MID Function
Using MID function, we are splitting text in the middle of a cell.
The result shown as the 1 letter in the middle of cell A2, start at the 6th letter.
Figure 3. Example of RIGHT Function
The same logic with LEFT function will apply to RIGHT function, the difference is starting from the right side of the cell.
- Applying the LEFT and RIGHT function when you want to split characters starting at the left and right side of cells, respectively.
- LEFT, RIGHT and MID functions will split digits from the text, numbers, and special characters.
- Number formatting (for example:$) is not part of a number so will not be counted or extracted.