Extract substrings from a comma separated string using formulas in Excel

Question

I have this string in cell A1:

A1 some text, to be, processed

I want to create formulas for B1, C1, D1, etc. where:

B1 some text
C1 to be
D1 processed
E1 

I already found the Text to Columns feature, but I need to do this using formulas.

Answer 1

You can use VBA to create a custom function that can be used as a formula. To do this:

1. Open up Visual Basic (Alt+F11)
2. Create a new module (Insert->Module)
3. Copy/paste the following code:

Public Function custom_split(str As String, num As Long, Optional delimeter As String = " ") As String
    Dim substrs() As String
    substrs = split(str, delimeter)
    If UBound(substrs) < num Then
        custom_split = ""
    Else
        custom_split = Trim(substrs(num))
    End If
End Function

Now close the Visual Basic window to go back to Excel.

To use the formula, as per your example, type

• in B1: =custom_split(A1, 0, ",")
• in C1: =custom_split(A1, 1, ",")
• etc...

You can change the comma to split by other characters.

The only downside to custom formulas is that you will get a warning to enable macros whenever you open the file.

Answer 2

You can use the text functions Left, Mid and Right. Left lets you extract a number of characters from the left side of the text string. Right does the same thing from the right side of the text string. With Mid, you exclude some number of characters from the left, then tell it to take the next x number of characters.

If you go to the "insert Function" dialog box (click the "fx" next to the formula bar), you'll find these functions under the "Text" category.