I want a function in excel that i can call and pass a cell into. Input:
Firstname Lastname [email protected]
Firstname midname Lastname [email protected]
The number of spaces in between are random. Output should just be an array. The array can have any length since i don't know what the strings look like. Output should be:
Firstname, Lastname, [email protected]
Firstname, midname, Lastname, [email protected]
I will call the function from one cell like =MySplitFunction(A1)
, and that should put Firstname in A1, Lastname in B1, and [email protected] in C1. I created a new module and tried the following code:
Function MySplitFunction(s As String) As String()
MySplitFunction = Split(s, " ")
End Function
Which gives me output
Firstname
How do i get it to return the whole array? Is it even possible to write a function in one cell that will put stuff in cells close to it?
EDIT:
Alternative solution is to:
This is proposed function:
Public Function MySplitFunction(sMark As String, nTh As Integer) As String
On Error GoTo EH
'regexp declaration
Dim objRegExp As Object
Set objRegExp = CreateObject("vbscript.regexp")
Dim tmpTXT As String
Dim tmpArr As Variant
With objRegExp
.Global = True
.Pattern = "\s+"
tmpTXT = .Replace(sMark, " ")
End With
tmpArr = Split(tmpTXT, " ")
MySplitFunction = tmpArr(nTh - 1)
Exit Function
EH:
MySplitFunction = ""
End Function
and this is screen shot presenting how it works:
Important! when calling function in Excel use comma to separate parameters (instead of presented semi-colon due to local-national version of excel I use).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With