Split String by colon, semi-colon, comma, or delimiter

This quick example will demonstrate how to split a string by a colon, semi-colon, comma, or delimiter in Excel. The example below will split a name from the corresponding phone number using the Left Function and Right Function.

Split string by colon
Split string by colon

Split String by Colon: Left Function Example

Let’s begin by looking at the data set. In this example, we will use the LEFT Function and Right Function to separate the name and phone number from the data set in column A.

In Cell C2, enter the following formula. This will look for the string of characters up to the “:” and return everything before. Drag the formula down to complete the list.

=LEFT(A2, SEARCH(":", A2,1)-1)
Left Function in Excel

Split String: Right Function Example

Now, let’s separate the phone number from the string using the Right Function. This is achieved by using the following formula. This formula checks for the position of the Colon, adds 1 space, then returns everything after.

=RIGHT(A2,LEN(A2)-FIND(":",A2))
Right Function in Excel

If you have any questions, check out our Discourse Forum for help.

Start the discussion on Excelbuddy's Discourse

Scroll to Top
Send this to a friend