Insert cell text every 5 rows in a column

M

Mike Mike

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
 
V

VBA Noob

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
 
E

Excelenator

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.
 
E

Excelenator

Both solutions have merit, however VB Noob's replaces every fifth numbe
with text instead of adding it to the next row and continuing th
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

-------------------
 
M

Mike Mike

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
 
V

VBA Noob

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
 
T

Toppers

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
 
M

Mike Mike

Awesome!!!
Thanks..

Mike



Toppers said:
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
Click to expand...
 
Top