10 columns of phone numbers extracted <WORKING LINK>

[HockeyDiablo]

I have 10 columns of data and would like them all organized, any tips?


[TABLE="width: 1000"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]111-111-1111[/TD]
[TD][/TD]
[TD]222-222-2222[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]333-333-3333[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]444-444-4444[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]555-555-5555[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]666-666-6666[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]777-777-7777[/TD]
[TD][/TD]
[TD][/TD]
[TD]888-888-8888[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]999-999-9999[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]111-111-1111[/TD]
[TD]222-222-2222[/TD]
[TD]333-333-3333[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]444-444-4444[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]555-555-5555[/TD]
[TD]666-666-6666[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]777-777-7777[/TD]
[TD]888-888-8888[/TD]
[TD]999-999-999[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



**NOT SURE WHY THE FIRST POST WAS BLANK?

 

[MickG]

Run of data sheet for results in sheet2.

[COLOR="Navy"]Sub[/COLOR] MG06Jul30
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = ActiveSheet.UsedRange.Resize(, 10)
ReDim nray(1 To UBound(Ray, 1), 1 To UBound(Ray, 2))
[COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray, 1)
    c = 0
    [COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray, 2)
        [COLOR="Navy"]If[/COLOR] Len(Ray(n, Ac)) [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            nray(n, c) = Ray(n, Ac)
           oMax = Application.Max(oMax, c)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(UBound(Ray, 1), oMax)
   .Value = nray
   .Borders.Weight = 2
   .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Marcelo Branco]

Select the range (A1:J4)
Press F5
Special…
pick Blanks
OK
Right-click one of the selected cells
pick Delete…
pick Shift cells left
OK

Hope this helps

M.

 

[HockeyDiablo]

Run of data sheet for results in sheet2.

[COLOR=Navy]Sub[/COLOR] MG06Jul30
[COLOR=Navy]Dim[/COLOR] Ray [COLOR=Navy]As[/COLOR] Variant, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] oMax [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
Ray = ActiveSheet.UsedRange.Resize(, 10)
ReDim nray(1 To UBound(Ray, 1), 1 To UBound(Ray, 2))
[COLOR=Navy]For[/COLOR] n = 1 To UBound(Ray, 1)
    c = 0
    [COLOR=Navy]For[/COLOR] Ac = 1 To UBound(Ray, 2)
        [COLOR=Navy]If[/COLOR] Len(Ray(n, Ac)) [COLOR=Navy]Then[/COLOR]
            c = c + 1
            nray(n, c) = Ray(n, Ac)
           oMax = Application.Max(oMax, c)
        [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]Next[/COLOR] Ac
[COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(UBound(Ray, 1), oMax)
   .Value = nray
   .Borders.Weight = 2
   .Columns.AutoFit
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Works like a charm, thank you

 

[Mike Szczesny]

Can you use something like this? Hope this helps.

Excel 2012
	A	B	C	D	E	F
1	111-111-1111		222-222-222			333-333-3333
2
3	111-111-1111	222-222-222	333-333-3333
Sheet1

Cell Formulas
Range	Formula
A3	{=IFERROR(INDEX($A$1:$H$1,SMALL(IF($A$1:$H$1<>"",COLUMN($A$1:$H$1)-COLUMN($A$1)+1),COLUMNS($A$3:A3)))," ")}
B3	{=IFERROR(INDEX($A$1:$H$1,SMALL(IF($A$1:$H$1<>"",COLUMN($A$1:$H$1)-COLUMN($A$1)+1),COLUMNS($A$3:B3)))," ")}
C3	{=IFERROR(INDEX($A$1:$H$1,SMALL(IF($A$1:$H$1<>"",COLUMN($A$1:$H$1)-COLUMN($A$1)+1),COLUMNS($A$3:C3)))," ")}
D3	{=IFERROR(INDEX($A$1:$H$1,SMALL(IF($A$1:$H$1<>"",COLUMN($A$1:$H$1)-COLUMN($A$1)+1),COLUMNS($A$3:D3)))," ")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MickG]

You're welcome