Access Excel custom split Function to delimit text

This tutorial shows how to create a custom Access Excel VBA custom Function to delimit text with the help of VBA “Split” Function instead of “Text to Columns”.

Delimit text in Excel (non-VBA)

In Excel worksheet, there is a function called “Text to Columns” (Data > Text to Columns) to separate a text by delimiter. For example, Cell A1 contains “a,b,c” and after split by comma, A1 contains “a”, B1 contains “b” and C1 contains “c”.

An alternative way to do that is using Left Function with Find Function to locate the delimiter.

Taking a,b,c as and example, the first string separated by comma would require the following formula to extract

=LEFT(A1,FIND(",",A1,1)-1)

Unfortunately, it is extremely difficult to extract b and c from a,b,c

But fortunately,  a VBA Function called “Split” serves this purpose without using long and complicated combination of formula.

In this tutorial, I will create a custom worksheet Function to split text with the help of VBA “Split” Function.

VBA code of Access Excel custom Function to delimit text

Public Function wSplit(sInput, seperator As String, n As Integer)
    wSplit = Split(sInput, seperator)(n)
End Function

VBA Split Function separates a text by delimiter into an array, for example, you can split a text a;b;c;d into array {a,b,c,d}, afterwards you can extract the desired array item (starting from 0).

To know more about VBA Split Function, click here to read more.

Syntax of Access Excel custom Function to delimit text

wSplit(sInput, seperator As String, n As Integer)
sInput Text to delimit
seperator Delimiter to delimit text
n The Nth substring you want to extract

If the selected criteria fail to search a string, #VALUE! will return.

Example of Access Excel custom Function to delimit text

Formula
=wSplit(“Mary,Ann,John”,”,”,0) Mary Return the first array item of split
=wSplit(“Mary,Ann,John”,”,”,1) Ann Return the second array item of split
=wsplit(“Mary,Ann,John”,”,”,3) #VALUE! The maximum item in the split array is 2, the 3rd item does not exist
=wsplit(“Mary,Ann,John”,”;”,2) #VALUE! Seperator “;” cannot be found

Outbound References

https://msdn.microsoft.com/en-us/library/6x627e5f%28v=vs.90%29.aspx

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *