copy / paste without skipping blanks

T

Todd

Hi,

I am trying to sort a worksheet that has all the data in one column. I need to seperate the numbers into one column and the text in another. I tried f5:Go To Special but when it pastes it skips blanks (box not checked) so nothing lines up anymore. How do I do this? Its a large file and I sure don't want to do this manually.

Tia

Todd
 
M

Mike Fogleman

Todd, a filter won't filter numbers or characters and keep the blanks
between them also. However, a formula in the columns where you wanted to
paste them, will do it. Let's say the data you want to sort is in column A,
starting in row 1,and you want the numbers in column B and the letters in
column C, and the blank rows still in place as they are in column A.
In cell B1 put the formula =IF(ISNUMBER(A1),A1,"") and fill-down that
formula to the end of the data.
Then in cell C1 put the formula =IF(ISTEXT(A1),A1,"") and fill-down.
To get rid of the formulas and leave only the results, select columns B&C
and copy/paste special over the same cells.

I hope this will help...Mike F


Todd said:
Hi,

I am trying to sort a worksheet that has all the data in one column. I
Click to expand...
need to seperate the numbers into one column and the text in another. I
tried f5:Go To Special but when it pastes it skips blanks (box not checked)
so nothing lines up anymore. How do I do this? Its a large file and I sure
don't want to do this manually.
 
T

Tom Ogilvy

Dim rng as Range, cell as Range
set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
for each cell in rng
if Application.IsNumber(cell) then
cell.offset(0,2).Value = cell
else
cell.offset(0,1).Value = cell
end if
Next

--
Regards,
Tom Ogilvy


Todd said:
Hi,

I am trying to sort a worksheet that has all the data in one column. I
Click to expand...
need to seperate the numbers into one column and the text in another. I
tried f5:Go To Special but when it pastes it skips blanks (box not checked)
so nothing lines up anymore. How do I do this? Its a large file and I sure
don't want to do this manually.
 
Top