Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA split string by spaces

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:

enter image description here

like image 714
Goatcat Avatar asked Aug 01 '13 11:08

Goatcat


1 Answers

Alternative solution is to:

  1. use RegEx as a first step to remove all spaces
  2. split result of step first based on single spaces left
  3. moreover, because you need to return different element of the text in different cells than additional function parameter will solved that.

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:

enter image description here

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

like image 134
Kazimierz Jawor Avatar answered Oct 15 '22 17:10

Kazimierz Jawor