Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

share|improve this question
up vote 3 down vote accepted

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 = ""
        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.

share|improve this answer
Great answer, welcome at Super User! – Arjan Feb 27 '10 at 10:14
Exactly what I was looking for, thank you! – Carlos Gutiérrez Feb 27 '10 at 17:28

I did this using Left() and some columns on a spare worksheet that counted characters until it found a comma. It looked somewhat like this:

    A                          B                C                D
1    Text                    1st Comma        2nd Comma        3rd comma
2 =(cell ref to text)&","   =find(",",a2,0)  =find(",",a2,b2) =find(",",a2,c2)

Referring to your original sheet:
B1: =Left(a1,NewSheet!B2)
C1: =Mid(A1, NewSheet!B2+1, NewSheet!C2-NewSheetB2)
D1: =Mid(A1, NewSheet!(C2+1 NewSheet!d2-Newsheet!c2)

In your NewSheet, do as many columns as you think could have commas in your text. If the number is large, you probably should use the VBA solution.

Note that I stuck a comma to the end of your string so that you don't get errors (#value if no comma in string). You can either test for a result greater than the length of the original string or you can use =iferror() to handle the =find() functions not finding a comma. Lastly use =Trim() if the strings aren't consistent using a space after a comma.

share|improve this answer

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.

share|improve this answer
Thanks. The problem is the , can be at any position. Anyway, using SEARCH, MID, intermediate columns to hold , positions and some nested IF s I was able to do this. – Carlos Gutiérrez Feb 27 '10 at 3:02
@Carlos, then please post the answer yourself to let others benefit as well? – Arjan Feb 27 '10 at 10:13
@Arjan - my formulas are so convoluted I doubt they will benefit anybody (even I can't read them today :-), dtang solution is a lot better – Carlos Gutiérrez Feb 27 '10 at 17:01

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.