Splitting a String with multiple delimiters

useruqK9 0
Viewed 42

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.

vba excel
1 Answers

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)

braX0
Related