Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy / paste without skipping blanks
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy / paste without skipping blanks
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy / paste without skipping blanks
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy & Paste Non Blanks Only | Excel Worksheet Functions | |||
Macro - Copy and Paste repeatedly skipping cells | Excel Discussion (Misc queries) | |||
copy a list and skipping blanks | Excel Worksheet Functions | |||
copy range of cells with blanks then paste without blanks | Excel Worksheet Functions | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) |