Delimit Column and Repeat Row Identifer
Results 1 to 2 of 2

Thread: Delimit Column and Repeat Row Identifer
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2012
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Delimit Column and Repeat Row Identifer

    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

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,834
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Delimit Column and Repeat Row Identifer

    Depending on your data in columns "A & B", try this for results in columns "C & D".
    Code:
    Sub MG08Aug14
    Dim n As Long, Rw As Long, Ray As Variant, t, c As Long, Sp As Variant
    Dim Rng As Range, Dn As Range
    Ray = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Resize(, 2)
    For Rw = 1 To UBound(Ray)
    For n = 1 To Len(Ray(Rw, 2))
       t = Mid(Ray(Rw, 2), n, 1)
        If Not Mid(Ray(Rw, 2), n, 1) Like "[A-Za-z0-9]" Then
            Mid(Ray(Rw, 2), n, 1) = ","
        End If
    Next n
    
    Sp = Split(Ray(Rw, 2), ",")
    For n = 0 To UBound(Sp)
        c = c + 1
        Cells(c, 3) = Ray(Rw, 1)
        Cells(c, 4) = Sp(n)
    Next n
    Next Rw
    
    End Sub
    Regards Mick

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •