VBA split string by spaces

Question

I want a function in excel that i can call and pass a cell into. Input:

Firstname          Lastname      email@mail.com       
Firstname      midname     Lastname      email@mail.com

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@mail.com       
Firstname, midname, Lastname, email@mail.com

I will call the function from one cell like =MySplitFunction(A1), and that should put Firstname in A1, Lastname in B1, and email@mail.com 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

assylias · Accepted Answer · 2013-08-01T12:12:35.850

13

Enter you input data in A1
Select the B1:D1 range
enter your formula =MySplitFunction(A1)
make it an array formula by pressing CTRL + SHIFT + ENTER instead of just ENTER.

To remove the multiple spaces, you could amend your code like this (not super efficient but works):

Function MySplitFunction(s As String) As String()
    Dim temp As String

    Do
      temp = s
      s = Replace(s, "  ", " ") 'remove multiple white spaces
    Loop Until temp = s

    MySplitFunction = Split(Trim(s), " ") 'trim to remove starting/trailing space
End Function

edited Aug 01 '13 at 12:12

answered Aug 01 '13 at 11:57

assylias

321,522
82
660
783

This kinda works, however I don't get rid of all the extra white spaces. Any ideas? – Goatcat Aug 01 '13 at 12:01
I guess i want to do something that replaces all multiple white spaces with ONE single white space, and then run Split(s, " ") on it. Thoughts? – Goatcat Aug 01 '13 at 12:03
I did get it to work, but I had to add ´s = Replace(s, Chr(160), " ")´ below the current replace row, because it contained so called "non-break spaces". I edited the answer to include this. Thank you very much for your help! – Goatcat Aug 01 '13 at 13:24

score 7 · Answer 2 · answered Aug 01 '13 at 12:39

7

Alternative solution is to:

use RegEx as a first step to remove all spaces
split result of step first based on single spaces left
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).

answered Aug 01 '13 at 12:39

Kazimierz Jawor

18,861
7
35
55

I enter the formula exactly as you did in B2, but i get error "You cannot change part of an array." – Goatcat Aug 01 '13 at 12:54
what about comma vs. semi-colon which I mentioned in last remark at the end of my answer? – Kazimierz Jawor Aug 01 '13 at 12:58
Check edit of original post. I don't think i should use comma. I usually always use semi-colon. – Goatcat Aug 01 '13 at 13:01
you right, semi-colon is ok for you. 1. comment `On error...` line temporarily and give my feedback which line is the error in the code. 2. answering your additional question (from edition)- you could improve this function to make it array function and call it next with Ctrl+Shift+Enter in excel. – Kazimierz Jawor Aug 01 '13 at 13:12
2

+1 good idea on the regexp. Adding a `CHAR(160)` component to the pattern also would be a good idea. – brettdj Aug 02 '13 at 03:58

VBA split string by spaces

2 Answers2

Linked