Transform Column to several columns depending on condition.

Came up with a form with text boxes to input the number of spaces before each level of the export and rearange the column with these values. 10 was the biggest number of different spaces.

Private Sub doit_butt_Click()
Dim Last_cell_num, a, b As Long
Dim temp1, temp2, temp3 As String
Dim set_space_filter As String
Dim get_space As Integer
Dim lev1, lev2, lev3, lev4, lev5, lev6, lev7, lev8, lev9, lev10 As String
lev1 = "0"
temp1 = ""
temp2 = ""
temp3 = ""
Dim len1, len2 As Integer
Dim lev1_tmp, lev2_tmp, lev3_tmp, lev4_tmp, lev5_tmp, lev6_tmp, lev7_tmp, lev8_tmp, lev9_tmp, lev10_tmp As String
    
lev2 = levf_2.Text
lev3 = levf_3.Text
lev4 = levf_4.Text
lev5 = levf_5.Text
lev6 = levf_6.Text
lev7 = levf_7.Text
lev8 = levf_8.Text
lev9 = levf_9.Text
lev10 = levf_10.Text
    With ActiveSheet
        Last_cell_num = .Cells(.Rows.count, 1).End(xlUp).Row
    End With
    For a = 1 To Last_cell_num
        temp1 = ActiveSheet.Range("A" & CStr(a)).Value
        len1 = Len(temp1)
        len2 = Len(temp1) - Len(Trim(temp1))
        
        If lev10 <> "not available" Then
            If len2 = CInt(lev1) Then
                lev1_tmp = Trim(temp1)
                lev2_tmp = ""
                lev3_tmp = ""
                lev4_tmp = ""
                lev5_tmp = ""
                lev6_tmp = ""
                lev7_tmp = ""
                lev8_tmp = ""
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev2) Then
                lev2_tmp = Trim(temp1)
                lev3_tmp = ""
                lev4_tmp = ""
                lev5_tmp = ""
                lev6_tmp = ""
                lev7_tmp = ""
                lev8_tmp = ""
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev3) Then
                lev3_tmp = Trim(temp1)
                lev4_tmp = ""
                lev5_tmp = ""
                lev6_tmp = ""
                lev7_tmp = ""
                lev8_tmp = ""
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev4) Then
                lev4_tmp = Trim(temp1)
                lev5_tmp = ""
                lev6_tmp = ""
                lev7_tmp = ""
                lev8_tmp = ""
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev5) Then
                lev5_tmp = Trim(temp1)
                lev6_tmp = ""
                lev7_tmp = ""
                lev8_tmp = ""
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev6) Then
                lev6_tmp = Trim(temp1)
                lev7_tmp = ""
                lev8_tmp = ""
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev7) Then
                lev7_tmp = Trim(temp1)
                lev8_tmp = ""
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev8) Then
                lev8_tmp = Trim(temp1)
                lev9_tmp = ""
                lev10_tmp = ""
            ElseIf len2 = CInt(lev9) Then
                lev9_tmp = Trim(temp1)
                lev10_tmp = ""
            ElseIf len2 = CInt(lev10) Then
                lev10_tmp = Trim(temp1)
            End If
''''' this continues a lot....
        End If
        
        If lev10 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
                ActiveSheet.Range("BD" & CStr(a)).Value = Trim(lev4_tmp)
                ActiveSheet.Range("BE" & CStr(a)).Value = Trim(lev5_tmp)
                ActiveSheet.Range("BF" & CStr(a)).Value = Trim(lev6_tmp)
                ActiveSheet.Range("BG" & CStr(a)).Value = Trim(lev7_tmp)
                ActiveSheet.Range("BH" & CStr(a)).Value = Trim(lev8_tmp)
                ActiveSheet.Range("BI" & CStr(a)).Value = Trim(lev9_tmp)
                ActiveSheet.Range("BJ" & CStr(a)).Value = Trim(lev10_tmp)
        ElseIf lev10 = "not available" And lev9 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
                ActiveSheet.Range("BD" & CStr(a)).Value = Trim(lev4_tmp)
                ActiveSheet.Range("BE" & CStr(a)).Value = Trim(lev5_tmp)
                ActiveSheet.Range("BF" & CStr(a)).Value = Trim(lev6_tmp)
                ActiveSheet.Range("BG" & CStr(a)).Value = Trim(lev7_tmp)
                ActiveSheet.Range("BH" & CStr(a)).Value = Trim(lev8_tmp)
                ActiveSheet.Range("BI" & CStr(a)).Value = Trim(lev9_tmp)
         ElseIf lev9 = "not available" And lev8 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
                ActiveSheet.Range("BD" & CStr(a)).Value = Trim(lev4_tmp)
                ActiveSheet.Range("BE" & CStr(a)).Value = Trim(lev5_tmp)
                ActiveSheet.Range("BF" & CStr(a)).Value = Trim(lev6_tmp)
                ActiveSheet.Range("BG" & CStr(a)).Value = Trim(lev7_tmp)
                ActiveSheet.Range("BH" & CStr(a)).Value = Trim(lev8_tmp)
         ElseIf lev8 = "not available" And lev7 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
                ActiveSheet.Range("BD" & CStr(a)).Value = Trim(lev4_tmp)
                ActiveSheet.Range("BE" & CStr(a)).Value = Trim(lev5_tmp)
                ActiveSheet.Range("BF" & CStr(a)).Value = Trim(lev6_tmp)
                ActiveSheet.Range("BG" & CStr(a)).Value = Trim(lev7_tmp)
         ElseIf lev7 = "not available" And lev6 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
                ActiveSheet.Range("BD" & CStr(a)).Value = Trim(lev4_tmp)
                ActiveSheet.Range("BE" & CStr(a)).Value = Trim(lev5_tmp)
                ActiveSheet.Range("BF" & CStr(a)).Value = Trim(lev6_tmp)
         ElseIf lev6 = "not available" And lev5 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
                ActiveSheet.Range("BD" & CStr(a)).Value = Trim(lev4_tmp)
                ActiveSheet.Range("BE" & CStr(a)).Value = Trim(lev5_tmp)
         ElseIf lev5 = "not available" And lev4 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
                ActiveSheet.Range("BD" & CStr(a)).Value = Trim(lev4_tmp)
         ElseIf lev4 = "not available" And lev3 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
                ActiveSheet.Range("BC" & CStr(a)).Value = Trim(lev3_tmp)
         ElseIf lev3 = "not available" And lev2 <> "not available" Then
                ActiveSheet.Range("BA" & CStr(a)).Value = Trim(lev1_tmp)
                ActiveSheet.Range("BB" & CStr(a)).Value = Trim(lev2_tmp)
         End If
    Next



End Sub

I will try with the sub you gave me.
Any suggestions how to reduce the code? Couse i am just beggining to do such stuff and it probably looks terrible

Damn it took me a lot of time to get this post....

THANKS for the reply. It does work great.