Insert cell text every 5 rows in a column
 

I need to go from this:
1
2
3
4
5
6
7
8
9
10
11

to this:
1
2
3
4
5
TEXT
6
7
8
9
10
TEXT
11

How can that be done??

Mike

Insert cell text every 5 rows in a column
 

Enter this in say cell A1 then drag down.

Copy and paster special values to get rid of formulas

=IF(MOD(ROW(),5)=0,"Text",ROW())


VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=568428

Insert cell text every 5 rows in a column
 

Place a 1 in the first cell and a 2 in the second cell. In the third
cell down place this formula and then copy it down as far as you want
to go.

=IF(ISNUMBER(A2),IF(MOD(A2,5)=0,"TEXT",A2+1),A1+1)

If you need actual values you can alwasy copy and paste values after
this is done.


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=568428

Insert cell text every 5 rows in a column
 

Both solutions have merit, however VB Noob's replaces every fifth number
with text instead of adding it to the next row and continuing the
numbers.


Code:
--------------------

Excelenator VB Noob
1 1
2 2
3 3
4 4
5 Text
TEXT 6
6 7
7 8
8 9
9 Text
10 11
TEXT 12
11 13
12 14
13 Text
14 16
15 17
TEXT 18
16 19
17 Text
18 21
19 22
20 23
TEXT 24

--------------------


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=568428

Insert cell text every 5 rows in a column
 

I think I might have given you guys a bad example.
There could be anything in the current cells and I need to insert "TEXT"
after every 5.
Lets try again:

I need to go from this:
apple
orange
widget
7845
santa
34blue
test
witch
never
45hold
74more

to this:
apple
orange
widget
7845
santa
TEXT
34blue
test
witch
never
45hold
TEXT
74more

Regards
Mike

Insert cell text every 5 rows in a column
 

Sorry,

Mis read your requested.

Enter 1 in A1 say and enter this in A2

=IF(MOD(ROW(),6)=0,"Text",COUNT($A$1:A1)+1)


VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=568428

Insert cell text every 5 rows in a column
 

Mike,
With data in column A , then put this in B1 and copy down:

=IF(MOD(ROW(),6)=0,"TEXT",INDIRECT("A"&ROW()-INT((ROW())/6)))

HTH

"Mike Mike" wrote:

I think I might have given you guys a bad example.
There could be anything in the current cells and I need to insert "TEXT"
after every 5.
Lets try again:

I need to go from this:
apple
orange
widget
7845
santa
34blue
test
witch
never
45hold
74more

to this:
apple
orange
widget
7845
santa
TEXT
34blue
test
witch
never
45hold
TEXT
74more

Regards
Mike

Insert cell text every 5 rows in a column
 

Awesome!!!
Thanks..

Mike



"Toppers" wrote in message
...
Mike,
With data in column A , then put this in B1 and copy down:

=IF(MOD(ROW(),6)=0,"TEXT",INDIRECT("A"&ROW()-INT((ROW())/6)))

HTH

"Mike Mike" wrote:

I think I might have given you guys a bad example.
There could be anything in the current cells and I need to insert "TEXT"
after every 5.
Lets try again:

I need to go from this:
apple
orange
widget
7845
santa
34blue
test
witch
never
45hold
74more

to this:
apple
orange
widget
7845
santa
TEXT
34blue
test
witch
never
45hold
TEXT
74more

Regards
Mike

Insert cell text every 5 rows in a column
 

Glad to help.

"Mike Mike" wrote:

Awesome!!!
Thanks..

Mike



"Toppers" wrote in message
...
Mike,
With data in column A , then put this in B1 and copy down:

=IF(MOD(ROW(),6)=0,"TEXT",INDIRECT("A"&ROW()-INT((ROW())/6)))

HTH

"Mike Mike" wrote:

I think I might have given you guys a bad example.
There could be anything in the current cells and I need to insert "TEXT"
after every 5.
Lets try again:

I need to go from this:
apple
orange
widget
7845
santa
34blue
test
witch
never
45hold
74more

to this:
apple
orange
widget
7845
santa
TEXT
34blue
test
witch
never
45hold
TEXT
74more

Regards
Mike

Hi..
i want to add an empty cell for a certain column having a data each 5 cells

before :

9286
9156
9334
8859
9319
29136
29633
29660
28143
29838
28508
28809
29106
27176
28807
10413
11408
11178

and i want it to be

9286
9156
9334
8859
9319

29136
29633
29660
28143
29838

28508
28809
29106
27176
28807

10413
11408
11178

Thanks in advance for the help