I am a supporter of St. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation to St. Joseph's, please.

Information on
St. Joseph's

 

Excel© lacks a worksheet function split which can split a cell content onto several cells with a given delimiter.

The user defined function WSSplit below solves this:

Function wssplit(s As String, _
    Optional sdelimiter As String = ",", _
    Optional lcount As Long = -1) As Variant
wssplit = Split(s, sdelimiter, lcount)
End Function

Another solution is the C++ Excel addin function sbSplit:

#include<cppinterface.h>
#pragma warning (disable : 4996)

#include <algorithm>
#include <iterator>

using namespace std;

CellMatrix // splits string into array
sbSplit(wstring s, // input string
          wstring delim, // delimiter
          const bool skip_empty = false // skip empty records if true
         )
// Splits input string s into array.
// Reverse("moc.LiborPlus.www") PB 0.11 06-Jan-2010
{
    CellMatrix result;
    if (delim.empty()) {
        result.PushBottom(s);
        return result;
    }
    wstring::iterator substart = s.begin(), subend;
    while (true) {
        subend = search(substart, s.end(), delim.begin(), delim.end());
        wstring temp(substart, subend);
        if (!(skip_empty && temp.empty())) {
            result.PushBottom(temp);
        }
        if (subend == s.end()) {
            break;
        }
        substart = subend + delim.size();
    }
    return result;
}