10 columns of phone numbers extracted <WORKING LINK>

[HockeyDiablo]

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

	A	B	C	D	E	F	G	H	I	J
1	111-111-1111		222-222-2222				333-333-3333
2		444-444-4444
3					555-555-5555				666-666-6666
4	777-777-7777			888-888-8888				999-999-9999

<tbody>
</tbody>

	A	B	C	D
1	111-111-1111	222-222-2222	333-333-3333
2	444-444-4444
3	555-555-5555	666-666-6666
4	777-777-7777	888-888-8888	999-999-999

<tbody>
</tbody>

**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