VBA 4th space

[Stephen_IV]

Good evening,

I am trying to find a VBA Function that will get everything after the 4th space. I know how to do this with a formula but I am learning VBA. I found something on the internet that finds the position but I am not good enough to modify it. Any help would be appreciated!

1 8:00 am 2 PARK AVE & WOOD AVE PARK AVE & WOOD AVE 2 8:04 am 4 TAFT AVE & PARK AVE TAFT AVE & PARK AVE 3 8:11 am 2 MADISON AVE & NORTH AVE MADISON AVE & NORTH AVE 4 8:15 am 5 CAPITOL AVE & MAIN ST CAPITOL AVE & MAIN ST 5 8:18 am 7 GARFIELD AVE & MADISON AVE GARFIELD AVE & MADISON AVE 6 8:21 am 2 FAIRVIEW AVE & WAYNE ST FAIRVIEW AVE & WAYNE ST 7 8:26 am 6 DEWHIRST ST & JEWETT AVE DEWHIRST ST & JEWETT AVE 8 8:29 am 5 ARDMORE ST & PLANKTON ST ARDMORE ST & PLANKTON ST 9 8:31 am 2 PARK AVE & ROOSTER RIVER BLVD PARK AVE & ROOSTER RIVER BLVD 10 8:34 am 7 DERAMO PL & MADISON AVE DERAMO PL & MADISON AVE

I found this code that finds the position on the 4th space.

Function FindN(s As String, sFindWhat As String, N As Integer) As Integer
Dim J As Integer
Application.Volatile
FindN = 0
    For J = 1 To N
        FindN = InStr(FindN + 1, s, sFindWhat)
        If FindN = 0 Then Exit For
    Next
End Function

 

[Marc L]

Hi,​

​

notice even under VBA I prefer to use an Excel formula for such basic need …​

​

Anyway an useless VBA function as required :​

Function AfterSpace4th$(Rg As Range)
     Dim S$(), C%
         S$ = Split(Rg.Text)
    If UBound(S) > 3 Then
         For C = 0 To 3:  S(C) = False:  Next
         AfterSpace4th = Join$(Filter(S, False, False))
    End If
End Function

​

In cell B1 :​

=AfterSpace4th(A1)

 

[Marc L]

A VBA demonstration extracting the text after the 4th space from column A to column B at once :​

Sub Demo1()
    With [A1].CurrentRegion.Columns
        .Item(2).Value2 = Evaluate(Replace("IFERROR(MID(#,FIND("" "",#,12)+1,99),"""")", "#", .Item(1).Address))
    End With
End Sub

 

[Rick Rothstein]

Give this VBA function a try...

Function After4thSpace(S As String) As String
  After4thSpace = Split(S & "    ", " ", 5)(4)
End Function

Edit Note: There are 4 spaces in the quote after the & symbol

 

[Stephen_IV]

Thank you both! I appreciate your help and guidance! All solutions worked great!