< Go Back

Split text string at specific character

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

Formula

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

=RIGHT(text, [num_chars])

Split text from the right of a string

=MID(text,[start_num],[num_characters])

Split text in the middle of a string

Arguments

  • 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.

Example 1

Figure 1. Example of LEFT Function

We can split text string by using LEFT function as the above example.

Input =LEFT(A2,4)

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.

Example 2

Figure 2. Example of MID Function

Using MID function, we are splitting text in the middle of a cell.

Input =MID(A2,6,1)

The result shown as the 1 letter in the middle of cell A2, start at the 6th letter.

Example 3

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.

Notes

  • 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.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar