Splitting a String with multiple delimiters-CodePudding

I want to split a string with multiple delimiters using Excel VBA. One of the strings is:

Raw String: Surat/Gujarat-India-East(Asia) Earth.
Intended Result: Surat Gujarat India East Asia Earth.

The problem is we cannot use multiple other delimiters while recording the Macros.

Any help will be appreciated.

CodePudding user response：

Here is a way easy to understand and add to.

It just changes all the delimiters to a space, and lastly removes any double spaces.

Put this into a MODULE.

Sub test()
  Dim s As String
  s = "Surat/Gujarat-India-East(Asia) Earth"
  
  s = Replace(s, "/", " ")
  s = Replace(s, "(", " ")
  s = Replace(s, ")", " ")
  s = Replace(s, " ", " ")
  s = Replace(s, "-", " ")

  s = Replace(s, "  ", " ")
  
  Debug.Print s

  ' Then if you want to actually split it...
  Dim v As Variant
  v = Split(s, " ")

End Sub

To use it as a function that returns an array which can also be used to set a range:

Function GetTest(s As String)
  's = "Surat/Gujarat-India-East(Asia) Earth"
  
  s = Replace(s, "/", " ")
  s = Replace(s, "(", " ")
  s = Replace(s, ")", " ")
  s = Replace(s, " ", " ")
  s = Replace(s, "-", " ")

  s = Replace(s, "  ", " ")
  
  GetTest = Split(s, " ")
  
End Function

Put your string Surat/Gujarat-India-East(Asia) Earth in A1

Then in B1, type =GetTest(A1)