the question: i'd like to split a large cell in to smaller, fixed-size cells (columns) by a standard formula.
example: consider cell A1 = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
i'd like a formula that created B1 = 'ABCDE' C1 = 'FGHIJ' D1 = 'KLMNO', etc
I used =Left (A1, 5) and got A1 = 'ABCDE' easily enough. I'm stumped at coming up with a formula that provides the next 5, and the next 5, etc.
the setup: if you want to know why i really need to solve the question i'm asking rather than using the split cell function:
i have a large set of data from a vendor. ideally, this worksheet will be used for other vendors that submit data similarly.
the script i'm writing takes a specific format--which i can't logically hold the vendors to. so, my specific formatting is on one sheet, referencing the data from each vendor on another sheet.
there is one field of data from vendors that is large ( >4000 characters, typically). however, this field makes my SQL server puke. i need to break this field down in to chunks of 1000 characters, and then re-concatenate after it leaves SQL to its final destination.
because i want to use some script automation to reuse this project, i'm reluctant to just use the split cell function to break up the data. rather i'd like to use whatever data is provided, and referentially split it up in my pre-formatted data.
example: consider cell A1 = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
i'd like a formula that created B1 = 'ABCDE' C1 = 'FGHIJ' D1 = 'KLMNO', etc
I used =Left (A1, 5) and got A1 = 'ABCDE' easily enough. I'm stumped at coming up with a formula that provides the next 5, and the next 5, etc.
the setup: if you want to know why i really need to solve the question i'm asking rather than using the split cell function:
i have a large set of data from a vendor. ideally, this worksheet will be used for other vendors that submit data similarly.
the script i'm writing takes a specific format--which i can't logically hold the vendors to. so, my specific formatting is on one sheet, referencing the data from each vendor on another sheet.
there is one field of data from vendors that is large ( >4000 characters, typically). however, this field makes my SQL server puke. i need to break this field down in to chunks of 1000 characters, and then re-concatenate after it leaves SQL to its final destination.
because i want to use some script automation to reuse this project, i'm reluctant to just use the split cell function to break up the data. rather i'd like to use whatever data is provided, and referentially split it up in my pre-formatted data.
