CODEWITHSUNDEEP
excelrowcell
RedHawkDK
RedHawkDK

Reputation: 139

excel - Splitting cell data into other cells based on a number in the first cell

I have one cell in excel containing the following data

108-0,109-1,110-0,111-2,112-0,113-192,114-87,115-100,116-80,117-60

I need to make a script or if statement, that can split the data from the cell into their own cells.

Like the data:

108-0 would be put into cell A108 with the data 0, and 109-1 would be put into cell A109 with the data 1.

How would that be accomplished? Any hints what I should read about?

Thanks in advance

I forgot to mention that the excel sheet should do it automatically. I i am importing several hundred of those 100-1 values, so they should be put into their own cells automatically.

Result - This worked for me:

Sub tst()
Dim X As Variant
X = Split(Range("A1").Value, ",")
Range("A1").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
End Sub

Upvotes: 1

Views: 552

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

You can use UDF:

Function splitThem(r As Range, delimeter As String)
    splitThem = WorksheetFunction.Transpose(Split(r, delimeter))
End Function

Just select A108:A117, enter next formula in formula bar =splitThem(A1,",") and press CTRL+SHIFT+ENTER to evaluate it

UPD:

let your values be in A1:D1 range, than you can use:

Sub test()
    Dim c As Range
    Dim str As Variant
    On Error Resume Next
    For Each c In Range("A1:D1")
        c.Offset(1).Resize(UBound(str)) = WorksheetFunction.Transpose(Split(c, ","))
    Next
End Sub

Upvotes: 1

Related Questions