I would like to copy a block of data to another column but add spaces between the data. Let’s say A1 = 1, A2 = 2, A3 = 3, A4 = 4, A5 = 5. I want to copy this to column C with a space between. C1 = A1, C3 = A2, C5 = A3, C7 = A4, C9 = A5. My actual spreadsheet has 700 lines of data. So, I am trying to figure out how to write the first 2 or 3 equations and fill the remainder. Any help would be appreciated.
Regards,
Tom
The replace function should be able to do that, check this:
http://spreadsheets.about.com/.....eplace.htm
Moreeg or Enthusiast will probably be along to suggest a formula.
I would do it with a macro like this to deal with exactly what you gave as an example
Sub MoveWithSpaces()
Dim rng As Range 'range of data to work on
Dim cel As Range 'individual cells in that range
Dim Lrow As Long 'last row of data to work on
Dim Crow As Long 'row to use in column C
Lrow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A1:A" & Lrow)
Crow = 1
For Each cel In rng
Range("C" & Crow) = cel.Value
Crow = Crow + 2
Next cel
End Sub
@Scorpion -that's an intriguing approach but not sure how it would be applied in this instance. Can you elaborate?
@nosparks - nice code and you did it in less time than it took me to come up with a formula. Here it is ...
@tparimore -
Place this formula in cell C1 and copy it down to the end of your data (actually farther than that - keep copying until the value in column C is consistently 0 or empty)
=IF(ODD(ROW())=ROW(),OFFSET($A$1,(ROW()+1)/2-1,0),"")
@nosparks - I can appreciate where you're coming from. It really is a matter of approach. My preference is always use built in functionality where possible on the basis that it is more transparent and easier to support for the average Excel user. In this case however it might be a toss-up as to which is better.
This topic has been closed to new replies. Please create a new topic instead.
