VBA Excel split the string apart based on the last comma

Question

I would like to split my string in Excel between the address and postcode. I want to keep the postcode separately.

By selecting the option - Data -Text to column - delimited - comma-separated - the whole string is divided by 4 pieces, as 3 commas occur.

1 - 21 Willow Court, 1192 Christchurch Road, Bournemouth, BH7 6EG

I found, that it can be done in VBA Excel.

There are a few approaches below:

Excel VBA- remove part of the string

https://www.thespreadsheetguru.com/the-code-vault/2014/2/28/remove-last-character-from-string

How to delete last character in a string with VBA?

Removing last characters vba

How to i remove a text after '*' or '-' character using VBA in excel?

I prepared the VBA code like below:

   Sub Textremove()
   Dim c As Variant
   For Each c In Range("D1:D100")
   c.Value = Left(c.Value, InStr(c.Value, ",") - 1)
   Next c
   End Sub

I am receiving only:

1 - 21 Willow Court

and the error Invalid procedure call or argument, debugging the following line:

     c.Value = Left(c.Value, InStr(c.Value, ",") - 1)

So the breakdown occurs after the first comma instead of the last one.

I found an answer regarding this error:

invalid procedure call or argument left

And when my code looks like this:

  Sub Textremove()
  Dim c As Variant
  For Each c In Range("D1:D100")
  If InStr(c.Value, ",") > 0 Then
  c.Value = Left(c.Value, InStr(c.Value, ",") - 1)
  End If
  Next c
  End Sub

Then error doesn't occur anymore, but I am still getting the stuff until the first comma instead of the last one.

When I change the code a bit:

 Sub Textremove()
 Dim c As Variant
 For Each c In Range("D1:D100")
 If InStr(c.Value, ",") > 0 Then
 c.Value = Right(c.Value, InStr(c.Value, ","))
 End If
 Next c
 End Sub

I am getting 2 sentences from the right

Bournemouth, BH7 6EG

which are not fixed and change depending on the total length of the string.

How can I receive the string till the last comma instead of the first one? How can I split the whole string between the address and postcode separately?

A good example is here:

https://trumpexcel.com/vba-split-function/

  Sub CommaSeparator()
  Dim TextStrng As String
  Dim Result() As String
  Dim DisplayText As String
  Dim i As Long
  TextStrng = Sheets("Final").Range("D1")
  Result = Split(TextStrng, ",", 1)
  For i = LBound(Result()) To UBound(Result())
  DisplayText = DisplayText & Result(i) & vbNewLine
  Next i
  MsgBox DisplayText
  End Sub

It admittedly splits the whole address, but it is counted still from the first comma.

Answer 1

I have sorted this in a different, 2-steps way.

First of all, I split a whole address, by using the formula from here:

Split address field in Excel

Sub Split()
  Dim MyArray() As String
  Dim Ws As Worksheet
  Dim lRow As Long, i As Long, j As Long, c As Long

  '~~> Change this to the relevant sheet name
  Set Ws = ThisWorkbook.Sheets("Final")

  With Ws
    lRow = .Range("E" & .Rows.Count).End(xlUp).Row

    For i = 1 To lRow
        If InStr(1, .Range("E" & i).Value, ",", vbTextCompare) Then
            MyArray = Split(.Range("E" & i).Value, ",")
            c = 1
            For j = 0 To UBound(MyArray)
                .Cells(i, c).Value = MyArray(j)
                c = c + 1
            Next j
        End If
      Next i
   End With
 End Sub

and next, I merged what I needed by using this hint:

Excel macro to concatenate one row at a time to end of file

Sub Merge()
Dim LastRow As Long
Dim Ws As Worksheet

Set Ws = Sheets("Final")

LastRow = Ws.Range("A" & Ws.Rows.Count).End(xlUp).Row

'~~> If your range doesn't have a header
Ws.Range("H1:H" & LastRow).Formula = "=A1&B1&C1"

'~~> If it does then
Ws.Range("H2:H" & LastRow).Formula = "=A2&B2&C2"
End Sub

and finally, I received:

1 - 10 Haviland Court 104 Haviland Road Bournemouth

Answer 2

In case you need VBA, maybe use:

Sub Test()

Dim str As String
Dim arr As Variant

str = "1 - 21 Willow Court, 1192 Christchurch Road, Bournemouth, BH7 6EG"
arr = Split(StrReverse(Replace(StrReverse(str), ",", "|", , 1)), "|")
    
End Sub

I reversed the whole string through StrReverse(), then used Replace() to replace only the 1st comma with a pipe-symbol (note the use of the Count parameter), reversed the string back and used a Split(). This returns:

---

An alternative would be to make use of the worksheetfunction REPLACE() instead of the VBA function which inconveniently is called the same.

Sub Test()

Dim str As String: str = "1 - 21 Willow Court, 1192 Christchurch Road, Bournemouth, BH7 6EG"
Dim arr As Variant

arr = Split(Application.Replace(str, InStrRev(str, ","), 1, "|"), "|")

End Sub

The main difference is now that Application.Replace does take a parameter to start the replacement at without cutting of the preceding text. We can find our starting position using InstrRev().

---

Both options return:

---

Just for fun I'll chuck in an regex solution:

Sub Test()

Dim str As String: str = "1 - 21 Willow Court, 1192 Christchurch Road, Bournemouth, BH7 6EG"
Dim arr As Variant

With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "^.*(?=,)|[^,]+$"
    Set arr = .Execute(str)
End With

End Sub

This will return a "MatchCollectionObject" where you can call your results through: arr(0) and arr(1). A little bit of explaination of the pattern:

• ^ - Start string anchor.
• .* - A greedy match of anything other than newline up to:
• (?=,) - Positive lookahead for a comma.
• | - Or match:
• [^,]$ - Anything other than comma up to the end string anchor.

See the online demo

Answer 3

In my case that works. I just added the UBound(Result())-1.

Sub CommaSeparator()
  Dim TextStrng As String
  Dim Result() As String
  Dim DisplayText As String
  Dim i As Long
  TextStrng = Sheets("Final").Range("D1")
  Result = Split(TextStrng, ",")
  For i = LBound(Result()) To UBound(Result()) - 1
  DisplayText = DisplayText & Result(i) & vbNewLine
  Next i
  MsgBox DisplayText
End Sub

Answer 4

Use the array returned by Split to rebuild the string however you like it e.g.:

Sub DoSplit()

s = "1 - 21 Willow Court, 1192 Christchurch Road, Bournemouth, BH7 6EG"
a = Split(s, ",")
finalString = a(0) & a(1) & a(2) & ", " & a(3)
MsgBox finalString

End Sub