Macro that splits content from cell if given character is found

[andrei]

Say i have column A with :

A1 : mother , father
A2 : father , son , daughter , mother
A3 : uncle , nice
A4 : John , Mary

The given character is , ( comma)

Actually i need 2 macros :

1. takes into account every comma , splits the content in different cells
like that
B1 : mother
B2 : father
B3 : father
B4 : son
B5 : daughter
B6 : mother
B7 : uncle
B8 : nice
B9 : John
B10 : Mary

2 . Macro number 2 which takes into account in a cell not every comma but
from 2 to 2 . Comma number 1 not taken in consideration . Comma number 2
taken . Comma number 3 not taken , comma number 4 taken
Something like this :

B1 : mother , father
B2 : father , son
B3 : daughter , mother
B4 : uncle , nice
B5 : John , Mary

So , the comma between son and daughter in cell A2 is in this case the only
comma considered as special character .
I don't know if this can be done . Macro 1 is more important to me . In 99%
of cases i am in that situation

 

[Jacob Skaria]

Try the below

Sub ReFormat1()
Dim c As Range, lngDRow As Long, arrData As Variant
lngDRow = 1
For Each c In Range("A1:A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row)
arrData = Split(c, ",")
Range("B" & lngDRow).Resize(UBound(arrData) + 1) = _
WorksheetFunction.Transpose(arrData)
lngDRow = lngDRow + UBound(arrData) + 1
Next
End Sub

Sub ReFormat2()
Dim c As Range, lngDRow As Long, arrData As Variant
lngDRow = 1
For Each c In Range("A1:A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row)
arrData = Split(c, ",")
For intcount = 0 To UBound(arrData) Step 2
Range("B" & lngDRow) = Trim(arrData(intcount)) & "," & Trim(arrData(intcount
+ 1))
lngDRow = lngDRow + 1
Next
Next
End Sub

If this post helps click Yes

 

[andrei]

Macro 1 works fine . Many thanks

Macro 2 gives me a syntax error for this :

Range("B" & lngDRow) = Trim(arrData(intcount)) & "," & Trim(arrData(intcount
+ 1))

 

[Jacob Skaria]

A line got split into two...Try the below version

Sub ReFormat2()
Dim c As Range, lngDRow As Long, arrData As Variant
lngDRow = 1
For Each c In Range("A1:A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row)
arrData = Split(c, ",")
For intcount = 0 To UBound(arrData) Step 2
Range("B" & lngDRow) = Trim(arrData(intcount) & "," & arrData(intcount + 1))
lngDRow = lngDRow + 1
Next
Next
End Sub

If this post helps click Yes

 

[andrei]

Thanks , now it works !