Excel VBA split after second space

Question

I'm trying to split my column so that the names

James John Doe

Comes out as only

James John

Using the below formula but it only leaves the first name, where I want it to split at the second occurrence of "space".

Sub Split1()
Dim r As Range

For Each r In Range("A2:A" & Cells(Rows.count, "A").End(xlUp).Row).Cells.SpecialCells(xlCellTypeConstants)
    r.Value = Split(r.Value, " ")(0)
    
Next r

Can anyone help me out?

Thanks

K.Dᴀᴠɪs · Answer 1 · 2021-06-02T05:17:00.760

2

There are a few ways you can turn a three-word phrase into the first two words.

Let's start with your Split() method. This function returns an array. Your particular method of attempting to access the index will only return a single word.

You can place into an array, then just combine the array elements:

For Each r In Range(...)

    Dim retVal() As String
    retVal = Split(r.Value)
    r.Value = retVal(0) & " " & retVal(1)

Next r

You can remove the last word with Replace():

For Each r In Range(...)

    r.Value = Replace(r.Value, Split(r.Value)(2), "")

Next

Or you can even use Regular Expressions:

With CreateObject("VBScript.RegExp")

    .Pattern = "\s[^\s]+$"
    For Each r in Range(...)
        r.Value = .Replace(r.Value, "")
    Next
    
End With

In Regular Expressions, \s signifies a single space character, the [^...] bracket means "Do not include", which we placed a \s within the bracket, so that would match any non-space character, followed by the + means 1 or more times, and finally the $ signifies the end of the string. Essentially, you are wanting to match a word [^\s]+ that is at the end of the string $, preceeded by a space \s, and remove it via the .Replace() method. And you actually could also simply use the pattern \s\S+$, which is essentially the same thing (\S means any non-space character when it's capitalized).

edited Jun 02 '21 at 05:17

answered Jun 02 '21 at 05:07

K.Dᴀᴠɪs

9,945
11
33
43

Thanks K.David! I learnt a lot form this, tried them all out and they work perfectly. I was wondering, though, if there is a way for the last word to also be changed to a single character in your retval or pattern method? To change James John Doe to James J? – Kelvin C Jun 02 '21 at 05:37
Yeah you can just change the last part of the line to `Left(retVal(1), 1)`. – K.Dᴀᴠɪs Jun 02 '21 at 05:40
@KelvinC if you wanted to use the pattern method, you could replace the .Pattern line with: `.Pattern = "^(\S+\s\w).*"` and the replace line with `r.Value = .Replace(r.Value, "$1")` – K.Dᴀᴠɪs Jun 02 '21 at 05:55
And btw, if you want you can visualize and learn more about this exact regular expression and how it works, you can click here: [Your customized RegEx link by Regex101.com](https://regex101.com/r/MAGlHJ/1). There's an explanation section on this site to the right that you can review. – K.Dᴀᴠɪs Jun 02 '21 at 06:06
@K.Dᴀᴠɪs Loved your helpful overview :+); fyi posted a late approach just to enrich the choice of posted & valid solutions. – T.M. Jun 02 '21 at 18:07

score 1 · Accepted Answer · answered Jun 02 '21 at 05:06

1

You may try to use left & find to obtain the string value untill second space instead of split function

Code modification:

Dim r As Range
Dim s As String, newText As String
Dim Length As Long

For Each r In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Cells.SpecialCells(xlCellTypeConstants)
    s = r.Value
    Length = Application.WorksheetFunction.Find(" ", s, Application.WorksheetFunction.Find(" ", s) + 1)
    
    r.Value = Left(s, Length)
Next r

Sample output:

answered Jun 02 '21 at 05:06

Kin Siang

2,644
2
4
8

This worked fantastically, thanks a ton Kin! – Kelvin C Jun 02 '21 at 05:22
Welcome, similar to excel function :) – Kin Siang Jun 02 '21 at 05:30
1

`VBA` has its own functions e.g. to get the number of characters before the second occurrence of a space use `Length = InStr(InStr(1, s, " ") + 1, s, " ")` (similar to your solution) or to get the number of characters before the last occurrence of a space use `Length = InStrRev(s, " ") - 1)`. – VBasic2008 Jun 02 '21 at 05:44
@VBasic2008, ya i never know `instr` work like find, anyway I am happy with `find` function in excel, both can be interchanged to use – Kin Siang Jun 02 '21 at 05:53

T.M. · Answer 3 · 2021-06-02T18:09:53.260

1

Further way to extract the 1st and 3rd token of a split array

This approach profits from the advanced possibilities of Application.Index allowing to indicate any new row or columns order; the wanted columns are reflected here by the last 1-based (columns) argument Array(1, 3):

Function GetFirstLast(s As String) As String
    GetFirstLast = Join(Application.Index(Split(s), 0, Array(1, 3)))
End Function

Example call:

Debug.Print GetFirstLast("James John Doe")

resulting in

James Doe in the VB Editor's immediate window.

edited Jun 02 '21 at 18:09

answered Jun 02 '21 at 18:03

T.M.

9,436
3
33
57

1

Nice approach on this one. Would have never thought to use this – K.Dᴀᴠɪs Jun 02 '21 at 18:21
Thanks for the feedback; indeed, `Application.Index()` offers a whole universe of restructuring possibilities for 1-dim as well as 2-dim arrays *(c.f. indicated post listing [some pecularities](https://stackoverflow.com/questions/51688593/excel-vba-insert-new-first-column-in-datafield-array-without-loops-or-api-call/51714153#51714153))*. Note that `Application.Index` returns arrays 1-based (in case of 2-dim arrays it re-bases both dimensions). @K.Dᴀᴠɪs – T.M. Jun 03 '21 at 08:10

Excel VBA split after second space

3 Answers3