Delimit Column and Repeat Row Identifer

[Ravin]

Hi all,

I have two columns of data in excel

with the headings - Reference and Result

the first cell (A2) in column 1 is a unique identifier
the second cell (B2) is the result

the issue is that the data in B2 are multiple results delimited by either

a tab, quotes, space, asterisk, comma, pipe, semi colon, colon, space, or tab

what I need is a way to delimit column B into its separate results

but keep all the data in 2 columns, so repeat the reference for each result in a new row

e.g.

Reference Result
123456 A|B

Becomes

Reference Result
123456 A
123456 B

R

 

[MickG]

Depending on your data in columns "A & B", try this for results in columns "C & D".

[COLOR="Navy"]Sub[/COLOR] MG08Aug14
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, t, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
Ray = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Resize(, 2)
[COLOR="Navy"]For[/COLOR] Rw = 1 To UBound(Ray)
[COLOR="Navy"]For[/COLOR] n = 1 To Len(Ray(Rw, 2))
   t = Mid(Ray(Rw, 2), n, 1)
    [COLOR="Navy"]If[/COLOR] Not Mid(Ray(Rw, 2), n, 1) Like "[A-Za-z0-9]" [COLOR="Navy"]Then[/COLOR]
        Mid(Ray(Rw, 2), n, 1) = ","
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n

Sp = Split(Ray(Rw, 2), ",")
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
    c = c + 1
    Cells(c, 3) = Ray(Rw, 1)
    Cells(c, 4) = Sp(n)
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Rw

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick