Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that splits content from cell if given character is found
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that splits content from cell if given character is foun
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)) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that splits content from cell if given character is foun
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 --------------- Jacob Skaria "andrei" wrote: 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)) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that splits content from cell if given character is foun
Thanks , now it works !
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro which searches for a character . When found delets what's be | Excel Programming | |||
Find and replace a character in a cell with another cell's content | Excel Programming | |||
Excel found unreadable content: | Excel Discussion (Misc queries) | |||
checking the content of a cell if it starts with certain character | Excel Programming | |||
split cell content without using a specific character/sign | Excel Programming |