Hi Experts,
I Please go through the attached spreadsheet. I have data in column A and Column E & F shows the required output.
Many thanks for your help.
Regards,
BS
Hi Experts,
I Please go through the attached spreadsheet. I have data in column A and Column E & F shows the required output.
Many thanks for your help.
Regards,
BS
Nice approach would probably use regular expressions, but "quick and dirty" can be based on series of split functions.
starting with something like
sets_of_data_in_a_row = Split(Replace(Cells(currentrow, "B"), " ", ""), "&")
then splitting for , and finally checking (and splitting) for -
Is that enough suggestion, or shall I show sample code?
Best Regards,
Kaper
Sub splitcolumn() LR = Cells(Rows.Count, "A").End(xlUp).Row drow = 2 dcol = 4 For r = 2 To LR ID = Cells(r, 1) st = Cells(r, 2) arr = splitarr(st) For ii = 0 To UBound(arr) Cells(drow, dcol) = ID Cells(drow, dcol + 1) = arr(ii) drow = drow + 1 Next Next End Sub Function splitarr(s) pnum = posnum(s) arrs1 = Split(s, " & ") For jj = 0 To UBound(arrs1) pnum = posnum(arrs1(jj)) s1 = Left(arrs1(jj), pnum - 1) s2 = Right(arrs1(jj), Len(arrs1(jj)) - pnum + 1) arrs = Split(s2, ",") For j = 0 To UBound(arrs) p = InStr(s2, "-") If p > 0 Then n1 = Left(s2, p - 1) n2 = Right(s2, Len(s2) - p) ss = "" For i = n1 To n2 ss = ss & s1 & i & "," Next s3 = s3 & ss Else s3 = s3 & s1 & arrs(j) & "," End If Next Next s3 = Left(s3, Len(s3) - 1) splitarr = Split(s3, ",") End Function Function posnum(s) As Integer For i = 1 To Len(s) s1 = Mid(s, i, 1) If IsNumeric(Mid(s, i, 1)) Then posnum = i Exit For End If Next End Function
If solved remember to mark Thread as solved
Try the attached
多么想要告诉你 我好喜欢你
OK, I can see that ready-code not just-directions prevails
And we have two other approaches (string functions and regular expressions covered)
So let me show also my propsition of (quite compact, isn't it) code using cascade splitting:
Sub test() Dim outputrow&, i&, j&, k&, l&, prefix$, sets, singleset, sequence outputrow = 2 For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row sets = Split(Replace(Cells(i, "B"), " ", ""), "&") For j = LBound(sets) To UBound(sets) prefix = Left(sets(j), 2) singleset = Split(Right(sets(j), Len(sets(j)) - 2), ",") For k = LBound(singleset) To UBound(singleset) sequence = Split(singleset(k), "-") For l = sequence(0) To sequence(UBound(sequence)) Cells(outputrow, "E") = Cells(i, "A") Cells(outputrow, "F") = prefix & l outputrow = outputrow + 1 Next l, k, j, i End Sub
Last edited by Kaper; 02-06-2014 at 05:56 AM.
All provided solutions are unique and worked as per requirement. How to mark it solved.
Thanks for your help and time.
Regards,
BS
Last edited by BS Singh; 02-06-2014 at 03:17 PM.
Kaper, very good and compact code, but you assumed only 2 characters before numbers
Sure, only two, and exactly two.
It follows the sample and ... I know that in many cases it is just like that.
Two_letters_and_number codes are almost as popular as TLA (three-letter acronym) ;-)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks