rates table vba conversion

[devplan]

I have an Excel rates table that I need to convert.
The original structure is, 1st column dial prefix, comma separated list.
2nd column is country name and 3rd column is call rate.
Like this:

44498, 4471, 4472, 4473, 4474, 447509, 447520, 447532, 447537, 447555, 447556, 447557, 447558, 447559, 44756, 44757, 44758, 4476, 447700, 447744, 447755, 447781, 447797, 447822, 447829, 447839, 447872, 447892, 447893, 447911, 447924, 447937, 447938, 447952, 47978, 447991, 447992, 447993, 447994, 447995, 447996, 447997, 447998	UK - Mobile	0.8099
447533, 447588, 447723, 447727, 447728, 447735, 447737, 447782, 447830, 447828, 447832, 447838, 447846, 447848, 447853, 447859, 447861, 447862, 447863, 447865, 447868, 447869, 447877, 447878, 447882, 447883, 447886, 447888, 447897, 447898, 447915, 447916, 447988	UK - Mobile Hi3G	0.7102

and so on.
However, for importing the list into a voip call accounting database I need to have for each dial prefix one separate entry.
So, what I did was saving the xls sheet as tab delimited text, then replacing in Word ,<space> to ^p (new para) and then import the whole result into Excel again.
This however leaves me the country name and rates cells unpopulated for the new dial prefix lines.
I saw in this forum a VBA solution to then autofil the unpoulated cells. In my case it would however need to autofill from the bottom on upwards because the unpopulated rows need to be filled with the next populated entry.

So - in short, the best would be a VBA to convert the above into populated line-by-line entries.

[JBeaucaire]

Put your comma-delimited values as demonstrated above into column A starting at A1.

Then run this macro on that data:

Option Explicit

Sub ParsePrefixes()
'JBeaucaire  (12/22/2009)
'Split comma-delimited prefixes into columnar format
'place last two values into adjacent column for each value
Dim LR As Long, NR As Long, i As Long, v As Long
Dim MyArr, MyArr2, buf As String
Application.ScreenUpdating = False

Range("B1:D1").Value = [{"Prefix","Country","Rate"}]
LR = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LR
    NR = Range("B" & Rows.Count).End(xlUp).Row + 1
    MyArr = Split(Cells(i, "A"), ",")
    Range("B" & NR).Resize(UBound(MyArr) + 1).Value = Application.WorksheetFunction.Transpose(MyArr)
    MyArr2 = Split(MyArr(UBound(MyArr)), " ")
    Range("B" & Rows.Count).End(xlUp) = MyArr2(1)
    Range("D" & NR, Range("B" & Rows.Count).End(xlUp).Offset(0, 2)) = MyArr2(UBound(MyArr2))
    For v = 2 To UBound(MyArr2) - 1
        buf = buf & MyArr2(v) & " "
    Next v
    Range("C" & NR, Range("B" & Rows.Count).End(xlUp).Offset(0, 1)) = Trim(buf)
    buf = ""
Next i

Application.ScreenUpdating = True
Columns(1).Delete xlShiftToLeft
Cells.Columns.AutoFit
End Sub

The macro goes into a regular module (Insert > Module)

[stanleydgromjr]

devplan,

Welcome to the ExcelForum.

Please post your workbook, with sample data, before and after worksheets - scroll down and see "Manage Attachments".