I've been struggling with this problem for a few days and was hoping i could find some help here.
I have a text file that when I copy into Excel ends up putting the entire text into cell A1 as one long string. These files can be thousands of characters long but each record is always 550 characters. I need to split this text into sections that are 550 characters long so the first 550 characters will go into cell A1 the second 550 characters go into A2 and so on until the text string is done.
I am at a total loss. Everything I've tried on here hasn't worked for me. But i would consider myself a novice at this.
I'm able to grab the first 550 using these formulas:
=LEFT(Sheet1!A1,550) and
=TRIM(LEFT(SUBSTITUTE(A1,"< /a >",REPT(" ",550),2),550))
As you can see this only gives me the first 550 characters.
Ideally I'd like this to be the end result:
A1 First 550 characters
A2 Second 550 characters
A3 Third 550 characters
A4 Forth 550 characters
A5 ECT until the whole string is broken out.
Any help would be greatly appreciated!
To split on every 550th character, with the raw data in A1:
=MID(Sheet1!$A$1,(ROW(1:1)-1)*550+1,550)
Put that in the first cell and copy down