SEARCH

The How-To Geek Forums Have Migrated to Discourse

How-To Geek Forums / Microsoft Office

Excel 2010 - Autofill add spaces

(6 posts)
  • Started 1 year ago by tparimore
  • Latest reply from moreeg
  • Topic Viewed 818 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
Top
 
scorpion99
scorpion99
Posts: 1033

The replace function should be able to do that, check this:
http://spreadsheets.about.com/.....eplace.htm

Posted 1 year ago
Top
 
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
Top
 
moreeg
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
Top
 
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
Top
 
moreeg
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
Top
 



Topic Closed

This topic has been closed to new replies.