Split, Cocnatenate and transpose

[maxxa666]

I have an excel table with a string that I need to do the following

1. split L2Code
2. concatenate L3Code
3. have all the codes in a single column field

L3DescL3CodeL2CodeOMB_Flag30082001 ; 2002 ; 2003 ; 2004 ; 2005 ; 2006 ; 2007 ; 2008 ; 2009 ; 2017OMB_Flag30082010 ; 2011 ; 2012 ; 2013 ; 2014 ; 2015 ; 2016 ; 2999 ; 2018 ; 2020 ; 2021Deadlock30152001 ; 2002 ; 2003 ; 2004 ; 2005 ; 2006 ; 2007 ; 2008 ; 2009 ; 2017Deadlock30152010 ; 2011 ; 2012 ; 2013 ; 2014 ; 2015 ; 2016 ; 2999 ; 2018 ; 2020 ; 2021WK 8 Letter30132001 ; 2002 ; 2003 ; 2004 ; 2005 ; 2006 ; 2007 ; 2008 ; 2009 ; 2017WK 8 Letter30132010 ; 2011 ; 2012 ; 2013 ; 2014 ; 2015 ; 2016 ; 2999 ; 2018 ; 2020 ; 2021Reopen_7D30092020 ; 2021Reopen_14D30102020 ; 2021Reopen_15D30112020 ; 2021


The result should look like below

200130082010300820013015201030152001301320103013

I have attached a sample of the data. any help would be appreciated

 

[CJ_London]

what is the logic you would use?

suggest repost highlighting the parts of the field you are looking for e.g.

L3DescL3CodeL2CodeOMB_Flag30082001 ; 2002 ; 2003 ; 2004 ; 2005 ; 2006 ; 2007 ; 2008 ; 2009 ; 2017OMB_Flag30082010

and assuming the above is correct, explain why you don't include the first 3008 and the overal logic of identifying the various components - e.g.' is found after alpha text'

and have you consider treating this a text file with a ';' field separator? i.e. importing rather than copy pasting?

 

[June7]

VBA in Excel. Put this code in module behind the worksheet then run with the Run button on VBA window:

Sub SplitData()
Dim x As Integer, y As Integer, z As Integer, strL3D As String, strL3C As String
Dim arrYrs As Variant
x = 2
z = 2
While Not IsEmpty(Range("A" & x))
    strL3D = Range("A" & x)
    strL3C = Range("B" & x)
    arrYrs = Split(Range("C" & x), ";")
    For y = 0 To UBound(arrYrs)
        Range("I" & z) = strL3D
        Range("J" & z) = strL3C
        Range("K" & z) = arrYrs(y)
        z = z + 1
    Next
    x = x + 1
Wend
End Sub

 

[Beetle]

I wasn't clear if you are trying to do this within Access or Excel, and the results you want are a bit out of the ordinary (I can see what you're trying to do, it's just unusual), so I just coded this in Excel. I added a button to your first worksheet (look at the code for the button). Column F is the original expected output from the file you uploaded. If you click the button it will copy those same results to Column G. The code could be adapted to Access if that's what you're really working in. I don't typically code in Excel, so if you are working in Excel there may be ways to clean up some of this, I don't know.

Warning: This works in your sample file but that doesn't necessarily mean that it will work in your real world scenario. I have no way of knowing if you could have blank rows or other anomalies that would cause errors. This is just an example to get you pointed in a direction. It will likely need some modification.

I have attached the modified Excel file.

Here is the code (in case anyone doesn't want to download the .xlsm file)

Sub Button1_Click()

    Dim X As Long, Y As Long, Z As Long, lngStartRow As Long, lngEndRow As Long, lngArrayEnd As Long, lngResultCell As Long
    Dim vArray
    Dim C1 As Range, C2 As Range, C3 As Range
    
    lngStartRow = 2
    lngEndRow = Range("B" & lngStartRow).End(xlDown).Row
    
    Z = 0
    
    'Need to get the array with the highest number of elements
    For X = lngStartRow To lngEndRow
        Set C1 = Range("C" & X)
        If InStr(C1, ";") > 0 Then
            vArray = Split(C1, ";")
            If UBound(vArray) > Z Then Z = UBound(vArray)
        End If
    Next X
            
        
    lngArrayEnd = Z
    lngResultCell = 2
    
    For Y = 0 To lngArrayEnd
        For X = lngStartRow To lngEndRow
            Set C1 = Range("B" & X)
            Set C2 = C1.Offset(0, 1)
            Set C3 = Range("G" & lngResultCell)
            If InStr(C2, ";") > 0 Then
                vArray = Split(C2, ";")
                If Y <= UBound(vArray) Then
                    C3 = Trim(vArray(Y)) & C1
                    lngResultCell = lngResultCell + 1
                End If
            Else
                'no delimited value in Column C of this row. Do something else here?
            End If
        Next X
    Next Y
    
End Sub

 

[Beetle]

Just saw June7 beat me to the punch. The result of June7's code would be slightly different than mine, but may be better if you are trying to import/modify the data in Access because it keeps it more normalized. Depends on what you're really trying to do I suppose.