how can I split data into multiple columns seperating only after the first colon in excel

Question

I have a huge excel list (20k+ rows) and want to split that one column list into multiple columns. Previously I used the Data|Text-to-Columns Wizard but it appears to have a limitation: I cannot specify after how many "splits" to stop to split. Example data:

type: abds : das
files: asdfs:jgkd

goal is:

|type|abds : das|
|files|asdfs:jgkd|

thanks for your time :)!

Answer 1

VBA is a great tool for small one off tasks like this.

Assuming data is in column A and is contiguous from A1. If this is not the case, simply adjust the line Set rng = ... to suit your data.

Sub SplitColumn()
    Dim rng As Range
    Dim dat As Variant
    Dim i As Long, j As Long

    Set rng = Range([a1], [a1].End(xlDown)).Resize(, 2)
    dat = rng
    For i = 1 To UBound(dat, 1)
        j = InStr(dat(i, 1), ":")
        If j > 0 Then
            dat(i, 2) = Mid$(dat(i, 1), j + 1)
            dat(i, 1) = Left$(dat(i, 1), j - 1)
        End If
    Next
    rng = dat
End Sub

Answer 2

You could replace the first colon with a "|" - or some other character that won't appear in your data - using:

=SUBSTITUTE(A1,":","|",1)

Then copy and paste the the results as values and do a Text To Columns on those values.

Answer 3

Normally you would write some vbscript to do this task, but if it is a one-column sheet, you could save it as Text (tab-delimited), then open it again. The Excel will ask you what kind of text file is it. Tell it that it is a file with Separators, click Next and tick Other and type a colon in the edit field.

Answer 4

Simplest way I can think of is to start with what you already did: the Data|Text-to-Columns Wizard .

You then re-concattenate the fields which were split unintendedly.