Solved how to copy and paste multiple cells

March 9, 2015 at 09:42:35
Specs: Windows 7
Pls tell me how to copy and paste the text of multiple cells in excel which are in regular intervals in one instance. for eg. i want to copy the text found in cells A5, A14, A23, A32, A41, A50, A59 etc

message edited by Rinno


See More: how to copy and paste multiple cells

Report •


#1
March 9, 2015 at 10:23:28
I think we need a little more information related to what you are trying to do.

You can simply hold down the Ctrl key as you select your cells and then Copy/Paste them. However, for some reason I doubt that you are looking for that simple an answer.

What exactly are you trying to accomplish?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
March 9, 2015 at 10:41:25
Thanks DerbyDad

Holding CTRL key is possible for selecting few cells.. But i have column A with 1126 rows .. i.e 1126 cells (with text) in column A.

For eg:

A
1 Anto
2 Ref No.:268617 
3 Korak Kahali 
4 Suite No. 12
5 Mumbai  
6 Maharashtra
7
8 Jessy
9 Ref No.:278649 
10 Alok grover 
11 1463,
12 sector 4
13 urban estate
14 Gurgaon 
15 Haryana 
.....
.....
....
1126 Paris

In that i need to copy the text(only cities) found in cells A5, A14, A23, A32, A41, A50, A59 etc and paste them in a separate column like this

B
Mumbai
Gurgaon
....
....
etc


Report •

#3
March 9, 2015 at 12:29:06
✔ Best Answer
You didn't include Row numbers for your column B output, so I'll assume a Column heading in Row 1 such that Mumbai should end up in B2.

Let's start with a Helper Column. You can put this column anywhere you like, I'll put it in Column C for simplicity sake.

In C1, enter this formula and drag it down as far as you need:

=ROW()*9-9

(Since you need every 9th row, you'll need 1/9th the total number of rows in Column A that contain data)

In Column C you should see this:

         C
1        0
2        9
3       18
4       27
5       36
6       etc.

In B2, enter this formula and drag it down as far as you need:

=OFFSET($A$5,C1,0)

What this will do is return the value in the cell that is OFFSET from A5 by the number of rows showing in C1, C2, C3, etc. Since Column C is incremented by 9 each time, the Column B formula will return ever 9th entry from Column A, starting from A5.

You can then do an Edit...PasteSpecial - Values if you want to lock the text into Column B, and then delete the Helper Column.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question