split concatenated string (with separator) to column (numeric)

[Cedric51]

hi all,

I am a very beginner in VB/Excel. In the column AI of the tab 'Main' in Excel, there is the string '163.31,185.00,206.52,228.33,249.94,271.53,293.14,314.90,336.46'. I want to split (delimiter = ',') the different values and create a column of numeric, for this example, I have to get the following column in AI
163.31
185.00
206.52
228.33
249.94
271.53
293.14
314.90
336.46

Of course, these values has to be numeric. any simple solutions?

Thank you.

Cedric

 

[CalcSux78]

Welcome to the forum!

Not sure how familiar you are with arrays, but a pretty easy task. Using the Split function, you can assign the values to an array, then send that array back to the worksheet.

Sub SplitString()
Dim rng As Range
Dim arr As Variant


Set rng = [AI1]
arr = Split(rng.Value, ",")
Set rng = rng.Resize(UBound(arr) + 1, 1)
rng = Application.Transpose(arr)
rng.NumberFormat = "0.00"
End Sub