• ARTICLES
• SUBSCRIBE
SEARCH

## Excel 2010 - Autofill add spaces

(6 posts)
• Started 1 year ago by tparimore
• Topic Viewed 687 times

tparimore
Posts: 1

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

Posted 1 year ago #

scorpion99
Posts: 1033

The replace function should be able to do that, check this:

Posted 1 year ago #

nosparks
Posts: 148

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

Posted 1 year ago #

moreeg
Posts: 842

@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),"")

Posted 1 year ago #

nosparks
Posts: 148

@moreeg - knew you'd show up with the solution.
Just can't get comfortable dragging something down 700 rows.

Posted 1 year ago #

moreeg
Posts: 842

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

Posted 1 year ago #

## Topic Closed

This topic has been closed to new replies. Please create a new topic instead.