Excel’s Split String Function: What is It and How Do You Use It?

excel split stringChances are, if you work in a large office setting, you are well aware of the existence of Microsoft Excel, a spreadsheet application, and use it on a regular basis. But there are those who aren’t too familiar with the software and have difficulty becoming acquainted with it because of its complex pivot table appearance and its unfamiliar graphing tools.

That is why here we will help break down the program, function by function, so that newer users can feel more comfortable working with the application – starting with the Split String Function. If you’re weary on going the “function by function” route Udemy offers a course for beginners that explores Excel 2013.  There are many different functions in Excel – so much so that when a professional is attempting to explain the application to a beginner, his or her head may spin. Attempting to explain the Split String function can be one such head scratching term, but we’ll make it easy, no matter your level of Excel expertise.

First of All, What is a String? 

In Excel, a string is considered text strings, which is a data type. A text string is the data that you enter into a cell in the program – like a name. For example, if you were to want to make a spreadsheet of your contacts, you would want to store their names, addresses, phone numbers, email addresses, etc. Each individual cell will hold that data. Those entries in the name column are considered text strings and Excel will see it as such.

Not all the data you put into a spreadsheet will be considered text strings or numbers. For example, if you put in a specific date into a cell, Excel will read it as a “date-time code”. You are able to format the date to where it can show 1/23/1983 or 1983-01-23 or even January 23, 1983. Excel will be able to perform this because it recognizes it as a type of data and not just a text string. Some of the time, a particular problem will pop up when you want to use tools which are specific to a particular format that actually does not work with the string, and this can be fairly frustrating so tools like this Udemy course on Excel Made Easy can help calm your frustration.

How do I Split a String? 

In order to split a string at a very specific location on the spreadsheet (like at the second character from the beginning), you use the Excel LEFT FUNCTION, MID FUNCTION, or the RIGHT FUNCTION. The Excel Left function returns a user specified number of characters directly from the left of the text string that’s supplied. The Mid function does the same thing as the left function, only it returns the number of characters from the middle of the supplied text string. Same with the Right function, only it returns the number of characters from the right.

If you decide that you would like to split a text string at the first occurrence of a specified character, there actually isn’t any built-in function that can perform this task. You perform this task using the Left, Mid, or Right function. You can also use the Excel search function (or the find function, as well) in order to find a position of a character inside a specific initial supplied string. Also remember that the search function is NOT case-sensitive (whereas the find function is case sensitive). The Excel Len function is also highly useful for splitting a string at the first occurrence of a specified character.

Formula 

You can always use a formula in order to split a text string at the first space, and then return to the left part of the split string, which can be done by combining the find function with the left function. So in the column next to the cell marked “test string”, input:

LEFT(A1,FIND(“”A1)-1)

In this formula, the Find function then returns the value 5 as point of origin within that supplied text. Therefore subtracting 1 would give it a value of 4, which will then be supplied to the Left function.

This may seem like a great deal of data lingo and phrasing, which is why Udemy offers this unique course for Excel beginners so you won’t be confused by the “data speak”.

The N’th Occurrence 

While using the Find or Search formula, you can hit a snag since both functions can only be used to find the first occurrence of a specified string of characters AFTER a start position is specified. So you obliviously cannot use either if you need to split your string at the N’th space. One way to abolish this little problem is to use the Excel Substitute function combined with the Excel Find or Search function in order to find the position of the N’th occurrence of a character.

The Substitute function actually substitutes the N’th occurrence of a specific string with a second string. You can then use the Find function to return the position and then supply this value to the Left, Mid, or Right function.

Try not to be too intimidated when exploring all the functions within Microsoft Excel or banging your head on the desk if you cannot figure out a problem or formula. Just like with any software that appears foreign to you, it will take time to learn.

Even the most advanced excel gurus need to brush up on their skills, which is why it’s important to stay up to date with a course specifically aimed at teaching advanced Excel users, such as that offered here on Udemy. It’s important to be patient with the program and play around with it at first in order to familiarize yourself with the application. You’ll find that the program is not quite as difficult as it appears, and you may even begin to use it on a daily basis in order to keep track of everything from your household budget to your contacts.