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: 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)
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))
If you have any questions, check out our Discourse Forum for help.
