Forum Discussion

dp3450's avatar
dp3450
Copper Contributor
Apr 03, 2021

Create custom columns in excel

I have an excel file with a column that says customfields. Data in this column are for multiple fields. I want to create a column for the each custom value in the excel file. Data in the custom col...
excel
HansVogelaar's avatar
HansVogelaar
MVP
Apr 03, 2021

dp3450 

Here is a macro you can run:

 

Sub SplitData()
    Const c1 = 3 ' Custom column = C
    Dim r As Long
    Dim m As Long
    Dim a() As String
    Dim i As Long
    Dim v() As String
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, c1).End(xlUp).Row
    With Range(Cells(2, c1), Cells(m, c1))
        .Replace What:="{", Replacement:="""", LookAt:=xlPart
        .Replace What:="}", Replacement:="""", LookAt:=xlPart
        .Replace What:=":""", Replacement:="""", LookAt:=xlPart
    End With
    For r = 2 To m
        a = Split(Cells(r, c1).Value, ",")
        If r = 2 Then
            Cells(1, c1 + 1).Resize(1, UBound(a)).EntireColumn.Insert
        End If
        For i = 0 To UBound(a)
            v = Split(Trim(a(i)), ":")
            If r = 2 Then
                Cells(1, c1 + i).Value = Trim(Replace(v(0), """", ""))
            End If
            Cells(r, c1 + i).Value = Trim(Replace(v(1), """", ""))
        Next i
    Next r
    Application.ScreenUpdating = True
End Sub

Resources