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.

You don't need VBA for this. What version of Excel have you got? Btw, kudos on the thorough question! Good research. — JvdV, Jan 21 '21 at 12:17
Hi, I know, that I don't need the VBA as it's A LOT of hints with excel formulas available. However I am automatizing something, so I need the VBA this time I am afraid. — Geographos, Jan 21 '21 at 12:24

score 1 · Answer 1 · answered Jan 21 '21 at 12:26

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

JvdV · Answer 2 · 2021-01-21T12:50:38.390

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

I tried two your codes and no error, but no reaction at all. I think instead of the fixed string I need something like: Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Final") Dim str As String: str = ws.Range("A" & ws.Rows.Count).End(xlUp).Row — Geographos, Jan 21 '21 at 14:15
You have found a way to loop over your cells. Now it shouldn't be too hard nomore to implement the above. You own attempt of splitting and throwing everything in seperate cells first is just redundant. @MKR. — JvdV, Jan 21 '21 at 14:21

score 0 · Answer 3 · answered Jan 21 '21 at 12:25

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

score 0 · Answer 4 · answered Jan 21 '21 at 14:08

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

VBA Excel split the string apart based on the last comma

4 Answers4