Macro that splits content from cell if given character is found
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
---------------
Jacob Skaria
"andrei" wrote:
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
|