Please Help - split a string having both comma (,) and semicolon(;)

**Chandan.jha** · 07-05-2012, 02:53 AM

Hi,

I need the function required to split the string seperated by both the Comma(,) and Semicolon(";"). Also please note that few of the segments can also have data in brackets seperated by comma(,) which shold not be split.

String - (Jan--01/Jun--30;EXPO(2000),July--01/Dec--31;NORM(100,200),.....)

Output -
date = Jan--01/Jun--30
data1 = EXPO(2000)
date2 = July--01/Dec--31
data 2 = NORM(100,200) ---> the numbers within bracets should not be split.

Thanks.
Chandan

**dmang1** · 07-05-2012, 03:17 AM

Hello Chandan,

Try the following formulae if the string is in A1.
The first date =MID(A1,2,FIND(";",A1,1)-2)
data1 =MID(A1,FIND(";",A1,1)+1,FIND(",",A1,B1)-FIND(";",A1,1)-1)
date2 =MID(A1,FIND(",",A1,FIND(";",A1,1))+1,FIND(";",A1,FIND(",",A1,FIND(";",A1,1))+1)-FIND(",",A1,FIND(";",A1,1))-1)
data2 =MID(A1,F1+1,FIND(",",A1,FIND(";",A1,1)+1))

Cheers,
Diana

If I've helped, click on the star to the left.

**NickyC** · 07-05-2012, 03:42 AM

Hi
this should do it by macro, if you prefer - you will need to set the input and output cells, and make sure the replace function uses a character you'll never have in your string (Where I used "@")

Sub parse_cells()
Dim InputCell As Range, OutputCell As Range, n As Long, RepIt As Boolean, txt As String, ArrNos As Variant, y As Long
Set InputCell = Range("A1")
Set OutputCell = Range("A10")
RepIt = False
txt = InputCell.Value
For n = 1 To Len(txt)
    If Mid(txt, n, 1) = "(" Then RepIt = True
    If Mid(txt, n, 1) = ")" Then RepIt = False
    If Mid(txt, n, 1) = "," And RepIt = True Then Mid(txt, n, 1) = "@"
Next n


txt = Replace(txt, ";", ",")
ArrNos = Split(txt, ",")
n = 0
For y = LBound(ArrNos) To UBound(ArrNos)
    OutputCell.Offset(n, 0).Value = Replace(ArrNos(y),"@",",")
    n = n + 1
Next y
End Sub

**Leith Ross** · 07-05-2012, 10:35 PM

Hello Chandan.jh,

Welcome to the Forum!

You gave only one example of the data you want to separate. You did not say whether the output would vertical (rows) or Horizontal (columns). This macro will correctly parse any text string according to your rules and output the results either vertically (the default) or horizontally to the cell of your choice.

The OutputDirection is optional. You can use one of two Excel built-in constants: xlByRows or xlByColumns. If you do not specify one of the constants then the default is by rows.


Sub ParseData(ByVal Text As String, ByRef OutputCell As Range, Optional OutputDirection As Long)

  ' Written: July 05, 2012
  ' Author:  Leith Ross (www.excelforum.com)
    
    Dim c As Long, m As Long
    Dim Matches As Object
    Dim n As Long, r As Long
    Dim RegExp As Object
        
        Select Case OutputDirection
            Case 0, xlByRows: n = 1: m = 0
            Case xlByColumns: n = 0: m = 1
        End Select
            
            Set RegExp = CreateObject("VBScript.RegExp")
            RegExp.Global = True
            RegExp.Pattern = "([^\,;]+)(\(\w+\,+\w+\))|([^\,;]+)"
        
            Set Matches = RegExp.Execute(Text & ",")
            
            For Each Match In Matches
                OutputCell.Offset(r, c) = Match
                r = r + n: c = c + m
            Next Match
            
End Sub

Here is an example of using the macro. The Text is on "Sheet2" cell "A1". The output is going to "Sheet1" cell "B1". All parsed data will start in "B1" and move across to "C1", "D1", etc.

    ParseData Worksheets("Sheet2").Range("A1"), Worksheets("Sheet1").Range("B1"), xlByColumns

**jindon** · 07-06-2012, 01:53 AM

try

Sub test()
    Dim r As Range, m As Object, i As Integer, n As Integer
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[\(,]([^;]+)|([^;]+\([^\)]+\))"
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            If .test(r.Value) Then
                n = 2
                For Each m In .Execute(r.Value)
                    For i = 0 To 1
                        If m.submatches(i) <> "" Then
                            n = n + 1
                            r(, n).Value = m.submatches(i)
                        End If
                    Next
                Next
            End If
        Next
    End With
End Sub