VBA Splitting a String into multiple cells when it has variable Delimiters

vba split multiple delimiters
excel vba split string into cells
vba split string into array of characters
vba split cell value by delimiter
vba split string by number of characters
vba split type mismatch
excel split function
vba array

If I have the below info all contained in a single cell and I want to split it into separate cells. I understand how to use the space as a delimiter but in this case, the name also has spaces and I want the name to stay together in a single cell. To further complicate the matter, the name is not always just first and last, it can also include middle so is not always a standard two names.

2172571122  Jane Doe 3143332222 John Doe

2172242237 Mary Mixer 2223334444 Mike M Martin

Want it to end up looking like this:

Cell 1 = 2172242237 
Cell 2 = Mary Mixer 
Cell 3 = 2223334444 
Cell 4 = Mike M Martin

Any suggestions?

You could try:

Option Explicit

Sub test()

    Dim strToSplit As String, strImport As String
    Dim arrwords As Variant
    Dim i As Long, counter As Long

    With ThisWorkbook.Worksheets("Sheet1")
        strToSplit = .Range("A1").Value
        arrwords = Split(strToSplit, " ")

        counter = 1

        For i = LBound(arrwords) To UBound(arrwords)

            If IsNumeric(arrwords(i)) = True Then
                strImport = arrwords(i)
                .Cells(3, counter).Value = strImport
                counter = counter + 1
            ElseIf Not IsNumeric(arrwords(i)) = True Then
                If Not IsNumeric(.Cells(3, counter - 1).Value) Then
                    strImport = .Cells(3, counter - 1) & " " & arrwords(i)
                    .Cells(3, counter - 1).Value = strImport
                    counter = counter
                Else
                    strImport = arrwords(i)
                    .Cells(3, counter).Value = strImport
                    counter = counter + 1
                End If
            End If

        Next

    End With

End Sub

Results look like this:

Excel VBA Split Function - Explained with Examples, VBA SPLIT function can be used to split text strings based on a delimiter. When working with VBA in Excel, you may have a need to split a string into different parts based each element of the 'Result' array assign it to the 'DisplayText' variable. This function takes one argument – the cell reference that has the address. Split function in VBA is a very useful string function which is used to split strings into multiple substrings based on a delimiter provided to the function and a comparison method, there are other string functions too which converts a string into substring but split function can split a string into more than one substrings.

This regex based function alternates each split between numbers and text (words).

Option Explicit

Function customSplit(str As String, _
                     Optional ndx As Integer = 1) As Variant

    Static rgx As Object, cmat As Object

    Set rgx = CreateObject("VBScript.RegExp")

    With rgx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        If CBool(ndx Mod 2) Then
            .Pattern = "[0-9]{10}"
            ndx = (ndx + 1) \ 2
        Else
            .Pattern = "[A-Z]{1,9}\s[A-Z]{1,9}[\s[A-Z]{1,9}]?"
            ndx = ndx \ 2
        End If
        If .test(str) Then
            Set cmat = .Execute(str)
            If ndx <= cmat.Count Then
                customSplit = cmat.Item(ndx - 1)
            End If
        End If
    End With

End Function

the split function, But what if the name you come across in cell A1 is something like David Lloyd George? Well, that's The first is the string you want to break up into separate pieces. After a The first two lines set up a String and an Integer variable, txt and i. The VBA Split Function is used is to split a string of text into an array. The text is split based on a given delimiter – e.g. a comma, space, colon etc. For example, imagine we have the following string: “Apple:Orange:Pear:Plum” You can see that each item separated by the colon sign. We call the colon sign the delimiter.

I have a few ideas on what you could do.

1) Read a Line

Do a split(line, " ") and loop through the indecies while performing a isNumeric() on each split value. If not, then add to a string Array() and set a flag to true.

Then, if isnumeric then, expect another name and set flag to true.

2) Read a line.

Then, loop through each character performing an isnumeric and if not then add that character to a string Array() and set flag until isnumeric again, etc....

I hope that helps or at least gets you in the right direction.

MS Excel: How to use the SPLIT Function (VBA), This Excel tutorial explains how to use the Excel SPLIT function with syntax and Excel SPLIT function will split a string into substrings based on a delimiter. The result is returned as an array of substrings. CELL (WS) · ENVIRON (VBA) · ERROR. In this example, the variable called LArray would now contain the array  The Excel VBA Split. The Excel VBA Split function was introduced in the year 2000, in response to a growing need to have a function that could handle large strings (for example, “John Harry David Smith” or “welcome to this tutorial”). The split function is used to split, or break, a string into manageable chunks.

Additional variant to posted already:

Sub ZZZ()
    Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")
    Dim num$, cl As Range, data As Range, key, x
    Dim Result As Worksheet
    Set data = Range([A1], Cells(Rows.Count, "A").End(xlUp))
    For Each cl In data
        x = "": num = "":
        For Each x In Split(cl, " ")
            If IsNumeric(x) Then
                num = x
                dic.Add x, ""
            ElseIf x <> "" And num <> "" Then
                dic(num) = Trim(dic(num) & " " & x)
            End If
        Next x
    Next cl
    Set Result = Worksheets.Add
    With Result
        .Name = "Result " & Replace(Now, ":", "-")
        x = 1
        For Each key In dic
            .Cells(x, "A").Value2 = key
            .Cells(x, "B").Value2 = dic(key)
            x = x + 1
        Next key
        .Columns("A:B").AutoFit
    End With
End Sub

test:

Excel VBA Split Function - A Complete Guide, The VBA Split Function is used is to break a string of text into an array. The text is split Glossary. Array – a structure for storing a group of similar variables. that is to be split. delimiter [optional] – the character delimiter used in the string to separate each item. Value = Split(s, ",") ' write the values to cells A1 to A4 Sheet1. Split can be defined as a function that can split a text string into an array, by making use of a delimiter character. It returns a zero-based, one-dimensional array holding the parts of the original text string. The Syntax of VBA Split Statement is as follows: Split (text_string, delimiter, limit, compare)

VBA SPLIT Function, VBA Split Function Examples. Example 1. The following VBA code splits the string "John Paul Smith" into three substrings, using the space character as a delimiter. #1 select the range of cells B1:B5 that you want to split text values into different columns. #2 go to DATA tab, click Text to Columns command under Data Tools group. And the Convert Text to Columns Wizard dialog box will open. #3 select the Delimited radio option in the first Convert Text to Columns Wizard dialog box, and click Next button.

Split cell values with Carriage Returns to Multiple Columns with VBA , The VBA.Split() function has two parameters, the first is the Active cells value and the second is vbLf (line feed) as a Delimiter. That is, each string or text, which is  When splitting cells in Excel, the key is to locate the position of the delimiter within the text string. Depending on your task, this can be done by using either case-insensitive SEARCH or case-sensitive FIND. Once you have the delimiter's position, use the RIGHT, LEFT or MID function to extract the corresponding part of the text string.

VBA Split Function (Examples), Split function in VBA is a very useful string function which is used to split strings into multiple substrings based on a delimiter provided to the function and a comparison Step 3: Now for the defined variable, My Text assigns the word “My Name is Excel VBA”. Step 3: Now apply VBA CELLS property and store the result. The SplitMultiDelims function is used to split a string into substrings using any of the supplied characters as delimiters between the substrings. Substrings are separated by any one character passed in the DelimChars characters.