how can I put a number from a sequence in every row in excel?

[diaExcel]

A B

Angola-Unitel Mobile 244-92
Anguilla-Digicel Mobile 1264-581, 1264-582, 1264-583, 1264-584
Anguilla-Mobile 1264-235, 1264-469, 1264-476, 1264-536
Anguilla-Other 1264
Antigua & Barbuda-Mobile 1268-464, 1268-72, 1268-764, 1268-770
Antigua & Barbuda-Other 1268
Argentina-Buenos Aires 54-11
Argentina-Central 54-230, 54-232, 54-291, 54-299, 54-342,
54-348

I have those sequences of numbers, first of all I want to take off the "-"
from those numbers it'll be looks like that: "1264581" not "1264-581", and
the big step, I want to put every number from a sequence (for exemple from
B2) bottom, it should be like this:

A B
Angola-Unitel Mobile 24492
Anguilla-Digicel Mobile 1264581
Anguilla-Digicel Mobile 1264582
Anguilla-Digicel Mobile 1264583
Anguilla-Digicel Mobile 1264584
Anguilla-Mobile 1264235
Anguilla-Mobile 1264469
Anguilla-Mobile 1264476
Anguilla-Mobile 1264536
Anguilla-Other 1264
Antigua & Barbuda-Mobile 1268464
Antigua & Barbuda-Mobile 126872
Antigua & Barbuda-Mobile 1268764
Antigua & Barbuda-Mobile 1268770
Antigua & Barbuda-Other 1268
Argentina-Buenos Aires 5411
Argentina-Central 54230
Argentina-Central 54232
Argentina-Central 54291
Argentina-Central 54299
Argentina-Central 54342
Argentina-Central 54348

thank you.

 

[Dave Peterson]

How about:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oRow As Long
Dim HowMany As Long
Dim mySplit As Variant
Dim myStr As String

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1 'no headers!
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow
myStr = .Cells(iRow, "B").Value
'replace the spaces and hyphens
myStr = Replace(myStr, " ", "")
myStr = Replace(myStr, "-", "")

'split the values based on the comma
mySplit = Split(myStr, ",")

'how many new rows do we need?
HowMany = UBound(mySplit) - LBound(mySplit) + 1

'assign the values to column A
NewWks.Cells(oRow, "A").Resize(HowMany, 1).Value _
= .Cells(iRow, "A").Value

'put those split values into column B
NewWks.Cells(oRow, "B").Resize(HowMany, 1).Value _
= Application.Transpose(mySplit)

'get ready for next record
oRow = oRow + HowMany

Next iRow
End With

'sort the new sheet by column A, then B
With NewWks.UsedRange
.Sort key1:=.Columns(1), order1:=xlAscending, _
key2:=.Columns(2), order2:=xlAscending, _
header:=xlNo
.Columns.AutoFit
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[diaExcel]

thank you so much, why I'm not thinking about it?, anyway thanks a lot

 

[Herbert Seidenberg]

Once you got your data arranged with Text to Columns
and Find/Replace, you can do "the big step" with
John Walkenbach's reverse pivot table at:
http://tinyurl.com/2xykg6