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”.

Access Excel custom Function to delimit text or split text

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”. Unfortunately, there is no worksheet “Function” to perform similar task, but there is a similar VBA Function called “Split” which serves this purpose.

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

Access Excel custom split Function to delimit text