This is my file path:

/Adobe/work/5.practices/5.1 Students/Reporting/studentReports/Ali's report/aliResult.pdf 

Now I want split them in columns like shown below:

| Adobe | work | 5.practices | 5.1 Students | Reporting | studentReports | Ali's report | aliResult.pdf |
  • 3
    Does it need to be a formula? You could you use the Text-to-column, setting the delimiter to "/" – Moacir Sep 10 '18 at 11:13
  • 1
    @Moacir, that's a great, simple solution. Why don't you post an answer? Even if it turns out to not meet the OP's requirements, it could be a good solution for other readers. – fixer1234 Sep 10 '18 at 11:25
  • @fixer1234 Done :) – Moacir Sep 10 '18 at 11:41
  • # Rehana, Do you need comma as suffix after been separated? If not edit the post. – Rajesh S Sep 10 '18 at 12:02

There is a tool in the Data tab in Excel that you may be able to use, called Text-To-Columns.

  • Select the cell with the data.
  • Select Text to Columns in the Data tab.
  • Choose the delimiter as "OTHER", and insert the character you want it to use as the split condition. In this case, the "/" (without quotes) or any other character you want to use.

With this, Excel will split the text in multiple columns for you in one step, and without the need for formulas.

BTW, you don't need to do this one cell at a time. You can select the whole column, do one Text to Columns operation, and Excel will do the entire list in one step.

  • I think the request for a "formula" wasn't necessarily meant literally. :-) – fixer1234 Sep 10 '18 at 11:44
  • But OP needs to separate them in columns with Comma as suffix – Rajesh S Sep 10 '18 at 11:44
  • @fixer1234, OP needs comma as Suffix while separate them. – Rajesh S Sep 10 '18 at 11:45
  • @RajeshS Thats the "other" part I mentioned. You can define which is the delimiter. It doesnt need to be a Comma – Moacir Sep 10 '18 at 11:45
  • 1
    @RajeshS, I think the commas were inserted only because the OP wrote it as a list in a sentence. I doubt the OP requires the commas. – fixer1234 Sep 10 '18 at 11:57

You can use formulas too, but I highly recommend Text to Columns.

If your main string is in A1, put this in A2 and drag right:

=COLUMN()&". "&TRIM(MID(SUBSTITUTE($A$1,"/",REPT(" ",99)),(COLUMN()+1)*99-98,99))

This answer shamelessly lifted from Rick Rothstein on this Forum

enter image description here


enter image description here

This simple UDF will spli & place values in Columns:

Function SplitValues(a As String, b As String)
  Dim Text() As String
  Text = Split(b, a)
  SplitValues = Text
End Function

How it works:

  1. Write this array formula then fill Right and press F2 & finish with Ctrl+Shift+Enter.



  1. Cell A6 will be blank because the Text starts with "/".
  2. Cell A2 has text to be spited.
  3. Adjust cell references in formula as needed.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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