CONCATENATE data to Sheet 2 Column

CONCATENATION is the process of taking separate data and putting it TOGETHER into once string, sometimes separated by a delimiter of some kind...like a comma. So you want to DE-concatenate.

You're example is greatly simplified. Are you sure you don't want to post up a simple workbook with some actual examples of the data including what you really want to see in BEFORE/AFTER columns. The account numbers are really to be tossed and renumbered from the beginning? You sure? Since we're going to write a macro, you could opt for a more specific format in the account numbers if you wish. Best to decide that now as we do it the first time.

So mockup a sample workbook showing the real data and your desired results (before/after), point out where the results came from if not obvious in your sample data.

Then click GO ADVANCED and use the paperclip icon to post up your workbook.

Hi aarbuckle

try...

Sub ptest()
    Dim i!, j!, c As Range, Rng As Range, z()
    Application.ScreenUpdating = False
    ReDim z(1 To 1, 1 To 1)
    Dim ws1    As Worksheet
    Set ws1 = Sheets("Reporting - Inputs Credit Pass")
    ws1.Activate
    Set Rng = ws1.Range("g5", Range("g" & Rows.Count).End(xlUp))
    i = 1
    For Each c In Rng
        For j = 1 To UBound(Split(Application.Clean(c.Value), "M")) + 1
            If Split(Application.Clean(c.Value), "M")(j - 1) <> "" Then
                z(1, i) = "M" & Split(Application.Clean(c.Value), "M")(j - 1)
                i = i + 1
            End If
            ReDim Preserve z(1 To 1, 1 To (i + 1))
        Next j
    Next c
    Sheets("Credit Pass (Feed)").Activate
    Sheets("Credit Pass (Feed)").Range("A5").Resize(i, 1) = Application.Transpose(z)
    Application.ScreenUpdating = True
End Sub

Hi aarbuckle

You can reply in the post no need to message me.

Please try the attachment

Hi aarbuckle
I'm not to sure what you mean ?
can you attachthe problem??