| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Insert rows based on specific value | Excel Worksheet Functions | |||
| insert date | Excel Worksheet Functions | |||
| Conditional Format as a MACRO | Excel Worksheet Functions | |||
| Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
| GET.CELL | Excel Worksheet Functions | |||
Hybrid Mode
