Split a string (cell) in Excel without VBA (e.g. for array formula)

by

Use XML functionality:

={SUM(FILTERXML("<t><s>" & SUBSTITUTE(A1, "#", "</s><s>") & "</s></t>", "//s"))}

Update April 2022: Use new TEXTSPLIT function.

More Related Contents:

  1. Excel Number Separation [closed]
  2. excel vba compare & copy worksheets from 2 excel files
  3. VBA Stuck after certain loop
  4. Is the . in .Range necessary when defined by .Cells?
  5. How to return a result from a VBA function
  6. Cycle through sub-folders and files in a user-specified root directory [duplicate]
  7. How to create a string or formula containing double quotes in Excel?
  8. Properly Handling Errors in VBA (Excel)
  9. Password hash function for Excel VBA
  10. Autofilter with column formatted as date
  11. Excel vba refresh wait
  12. How to correctly display .csv files within Excel 2013?
  13. What indicates an Office Open XML Cell contains a Date/Time value?
  14. Getting formula of another cell in target cell
  15. Excel vba to create every possible combination of a Range
  16. Excel VBA: getting row of clicked button [duplicate]
  17. Excel VBA Filter Change event handler
  18. How to copy only values in excel vba from a range?
  19. Run a Macro from Another Workbook
  20. Cannot VBA write data to cells in Excel 2007/2010 within a function
  21. Handle “No cells were found” error when filtered range is empty
  22. How to split texts from dynamic range?
  23. Excel: the Incredible Shrinking and Expanding Controls [closed]
  24. How do I use awk under cygwin to print fields from an excel spreadsheet?
  25. How to send email to multiple recipients with addresses stored in Excel?
  26. Generate Word Documents (in Excel VBA) from a series of Document Templates
  27. Excel Date Formatting
  28. Using Excel VBA Macro To Capture + Save Screenshot of Specific Area In Same File
  29. Function Overloading and UDF in Excel VBA
  30. VBA Excel simple Error Handling

Leave a Comment