How can I pick text cell every 3 rows

[Tizair55]

All my data is in one column. A1:A7785

I have text and number cells.

I just need to extract the data every 3 rows (A1,A4,A7,A10...) (A2,A5,A8,A11...) (A3,A6,A9,A12...) to 3 different columns.

Thanks for your help

 

[njimack]

Excel Workbook
	A	B	C	D
1	$A$1	$A$1
2	$A$2		$A$2
3	$A$3			$A$3
4	$A$4	$A$4
5	$A$5		$A$5
6	$A$6			$A$6
Sheet5

 

[bbott]

Welcome to the board. Try the following formulas in columns B, C, and D respectively:

=INDEX(A:A,ROW(A1)*3-2)

=INDEX(A:A,ROW(A1)*3-1)

=INDEX(A:A,ROW(A1)*3)

Copy down as needed.

 

[JoeMo]

If you'd prefer a macro solution, this one uses columns C:E for the split data:

Sub DataToThreeColumns()
Dim Vin As Variant, Vout As Variant, i As Long
Vin = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
ReDim Vout(1 To UBound(Vin, 1), 1 To 3)
For i = 1 To UBound(Vin, 1) - 2 Step 3
    Vout(i, 1) = Vin(i, 1)
    Vout(i, 2) = Vin(i + 1, 1)
    Vout(i, 3) = Vin(i + 2, 1)
Next i
Application.ScreenUpdating = False
With Range("C1:E" & UBound(Vout, 1))
    .Value = Vout
    .EntireRow.AutoFit
    .SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
End With
Application.ScreenUpdating = True
End Sub

 

[Tizair55]

Sheet5

*	A	B	C	D
1	$A$1	$A$1	*	*
2	$A$2	*	$A$2	*
3	$A$3	*	*	$A$3
4	$A$4	$A$4	*	*
5	$A$5	*	$A$5	*
6	$A$6	*	*	$A$6

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A1) C1 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A1) D1 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A1) B2 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A2) C2 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A2) D2 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A2) B3 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A3) C3 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A3) D3 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A3) B4 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A4) C4 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A4) D4 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A4) B5 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A5) C5 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A5) D5 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A5) B6 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A6) C6 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A6) D6 =IF(MOD(COLUMN()-1,3)-MOD(ROW(),3),"",$A6) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks It's help a lot

 

[Tizair55]

Thanks. The result is very good, I earned many time

Welcome to the board. Try the following formulas in columns B, C, and D respectively:

=INDEX(A:A,ROW(A1)*3-2)

=INDEX(A:A,ROW(A1)*3-1)

=INDEX(A:A,ROW(A1)*3)

Copy down as needed.