How to divide an excel sheet vertically into 4 parts?

**pahari75** · 02-16-2013, 02:01 AM

Dear All, thanks for reading the question.

I have many excel sheets with 1000 columns and 100,000 rows. I have to import these sheets into SAS system which wont let me import more than 250 columns per sheet (it misses the remaining columns, though rows it can import all of them). So, one solution is break each such sheets into 4 individual sheets. Ofcourse I can manually take the cursor to 250th column and copy/paste that data into another sheet and so on. But this is cumbersome and also means there is chance of mistake.
Is there a way I can divide the sheets into 4 sheets separately with each sheet having equal number of columns?

Another thing I need to do is that on the top row there are company codes -most of them start with a letter which is fine. There are few which start with a number and I have to add a dummy letter x before the number. Now since there are 1000 columns, I have to scan the top row of all 1000 columns to find number codes which are scattered unevenly. So I was wondering if there is a way to tell excel to change all such number codes with extra x behind each number?

Million thanks for your help.

**outofthehat** · 02-16-2013, 03:08 AM

You could use the OFFSET function, although with so many data points it might grind to a halt! If the raw data's on Sheet1, then in Sheet2 cell A1,
=OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1) for dataset 1
Sheet3 cell A1
=OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1+250) for dataset 2, etc
You'd still have to copy the formula into the right columns.
Despite my being averse to macros on grounds of auditability, maintenance, etc, this might be one time when a macro serves you well. You'll need advice from others on that, as I stay away from them as much as I can!

As for automatically avoiding the numbers, you can use =IF(ISNUMBER(A1),"x"&A1,A1) in row 2 to insert the x before any numbers.

**pahari75** · 02-16-2013, 08:27 PM

Thanks so much. I will try first without macros using offset function and will see.
Regards

**pahari75** · 02-19-2013, 06:07 PM

Hi, I tried it with the offset function, but it only gave me one value (date) in sheet 11 -My data is in sheet 10 and I used sheet10 in the formulae.
Can someone help me with this? Thanks a lot.