String Text to Columns

[ionelz]

[FONT=&quot]In my TABLE, Column1 have this type of Strings: A10 B15 A21 G33 B121 A1000 (THIS IS TEXT)[/FONT][FONT=&quot][/FONT][FONT=&quot]
I would like to SPLIT these Strings to Corresponded Columns, so 10 goes to Column A, 15 to Column B, 21 to Column A, 33 to Column G, 121 to Column B, 1000 to Column A, THIS IS A TEXT to Column2. So in Column A is 10,21,1000 in Column B is 15,121 in Column G is 33 and in Column2 THIS IS A TEXT. Text in () should always go to Column1[/FONT]

 

[D3allamerican07]

As long as the text within () is at the end of the cell, the following code should do what you're asking.

It first splits the cell that includes (THIS IS TEXT) to cells AA# and AB#, then loops through each part of the array, adding values to each respective column.

Let me know if you have any issues.

Sub ArrayLoop()

For i = 1 To Range("A" & Rows.Count).End(xlUp).row

    If InStr(1, Range("A" & i).Value, " (") Then
        cellSplit = Split(Range("A" & i).Value, " (")
        Range("AB" & i).Value = "(" & cellSplit(UBound(cellSplit))
        Range("AA" & i).Value = cellSplit(LBound(cellSplit))
        Range("A" & i).Value = ""
    Else
        Range("AA" & i).Value = Range("A" & i).Value
        Range("A" & i).Value = ""
    End If
    
    sArray = Split(Range("AA" & i).Value, " ")
    
    For x = 0 To UBound(sArray)
        cellColumn = Left(sArray(x), 1)
        cellValue = Right(sArray(x), Len(sArray(x)) - 1)
        
        If Range(cellColumn & i).Value = "" Then
            Range(cellColumn & i).Value = cellValue
        Else
            Range(cellColumn & i).Value = Range(cellColumn & i).Value & ", " & cellValue
        End If
    Next x
    
    If Range("AB" & i).Value <> "" Then
        Range("A" & i).Value = Range("A" & i).Value & ", " & Range("AB" & i).Value
    End If
    
Next i

End Sub

 

[István Hirsch]

Please show the requested layout manually.

 

[Rick Rothstein]

[FONT="]...and in [B][COLOR="#FF0000"]Column2[/B] THIS IS A TEXT. Text in () should always go to Column1[/FONT][/COLOR]

 

[ionelz]

I do not know VBA, so is hard for me to edit/debug
I would be happy if in my TABLE1 from Sheet1 I could do with a formula this : SPLIT the ColumnA with string in 2 columns, ColumnB and ColumnC
So ColumnA with String : N200 K30 V25 N12 B167 (THIS IS AN TEXT) will split in ColumnB : N200 K30 V25 N12 B167 and ColumnC : THIS IS AN TEXT
And say : ColumnB (Sheet1) to autofill to ColumnN(sheet2)

As long as the text within () is at the end of the cell, the following code should do what you're asking.

It first splits the cell that includes (THIS IS TEXT) to cells AA# and AB#, then loops through each part of the array, adding values to each respective column.

Let me know if you have any issues.

Sub ArrayLoop()

For i = 1 To Range("A" & Rows.Count).End(xlUp).row

    If InStr(1, Range("A" & i).Value, " (") Then
        cellSplit = Split(Range("A" & i).Value, " (")
        Range("AB" & i).Value = "(" & cellSplit(UBound(cellSplit))
        Range("AA" & i).Value = cellSplit(LBound(cellSplit))
        Range("A" & i).Value = ""
    Else
        Range("AA" & i).Value = Range("A" & i).Value
        Range("A" & i).Value = ""
    End If
    
    sArray = Split(Range("AA" & i).Value, " ")
    
    For x = 0 To UBound(sArray)
        cellColumn = Left(sArray(x), 1)
        cellValue = Right(sArray(x), Len(sArray(x)) - 1)
        
        If Range(cellColumn & i).Value = "" Then
            Range(cellColumn & i).Value = cellValue
        Else
            Range(cellColumn & i).Value = Range(cellColumn & i).Value & ", " & cellValue
        End If
    Next x
    
    If Range("AB" & i).Value <> "" Then
        Range("A" & i).Value = Range("A" & i).Value & ", " & Range("AB" & i).Value
    End If
    
Next i

End Sub

 

[ionelz]

Can I attached an EXCEL file ?

Please show the requested layout manually.

 

[István Hirsch]

Can I attached an EXCEL file ?

I use Excel Jeanie to do this, but there are other methods, refer to the 'Forum Rules' howto.

 

[ionelz]

Could you tell me how to attach an EXCEL file so I can better describe my question

 

[Rick Rothstein]

Could you tell me how to attach an EXCEL file so I can better describe my question

Post a copy of your workbook to DropBox, make sure it it shareable (I think you have to do that with DropBox), and then post the URL they give you in a response here.

 

[ionelz]

I did a Copy /Paste


[TABLE="width: 860"]
<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]String[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]G[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]A10 B20 G300 A103 K35 A48 A476 D12 A66 A76 (THIS IS AN TEXT)[/TD]
[TD]10,103,48,476,66,76[/TD]
[TD]20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]300[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]A49 C100 G2[/TD]
[TD]49[/TD]
[TD] [/TD]
[TD]100[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A10 C20 K21[/TD]
[TD]10[/TD]
[TD] [/TD]
[TD]20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]A40 B22 D21 A33[/TD]
[TD]40,33[/TD]
[TD]22[/TD]
[TD] [/TD]
[TD]21[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 7"]Split Numbers after character in Column String to "coresponded" Columns as shown[/TD]
[/TR]
[TR]
[TD="colspan: 7"]A - have Multiple Repetitions (6 MAX) , the rest B,C,D,E,F ar unique per String[/TD]
[/TR]
[TR]
[TD="colspan: 7"]If possible the SPLIT should stop before character ( so look only in the left side of character ([/TD]
[/TR]
</tbody>[/TABLE]