Excel: check length of string and split at 1000 characters and add and replace text

OK, so this involves an access script sql but I am converting it to vba. I have seen where this can be done using 2 textboxes and a commandbutton, but it does not process it the way that I want. The string is something like the following and it placed in textbox1 and the result should be in textbox2.

SELECT [PO - Stock items ordered].POID, [Stk - Stock items and Suppliers].StkItemID, [PO - Purchase orders].PODate, [PO - Stock items ordered].POSIDateDue, [PO - Purchase orders].CASID, [PMatCatDesc] & " " & [SMatCatDesc] & " " & [SizeDesc] & " " & [ColDesc] & " " & [SysDesc] AS Description, [CS - Client and Suppliers].CASCompanyName 
FROM ((((((([PO - Purchase orders] INNER JOIN ([PO - Stock items ordered] INNER JOIN [Stk - Stock items and Suppliers] ON [PO - Stock items ordered].SAMID = [Stk - Stock items and Suppliers].SAMID) ON [PO - Purchase orders].POID = [PO - Stock items ordered].POID) INNER JOIN [Stk - Stock items master] ON [Stk - Stock items and Suppliers].StkItemID = [Stk - Stock items master].StkItemID) INNER JOIN [Stk - Stock category - Primary] ON [Stk - Stock items master].PMatCatID = [Stk - Stock category - Primary].PMatCatID) INNER JOIN [Stk - Stock category - Secondary] ON [Stk - Stock items master].SMatCatID = [Stk - Stock category - Secondary].SMatCatID) INNER JOIN [Stk - Stock category - Size] ON [Stk - Stock items master].SizeID = [Stk - Stock category - Size].SizeID) INNER JOIN [Stk - Stock category - System] ON [Stk - Stock items master].SysID = [Stk - Stock category - System].SysID) 
INNER JOIN [Stk - Stock category - Colour] ON [Stk - Stock items master].ColID = [Stk - Stock category - Colour].ColID) INNER JOIN [CS - Client and Suppliers] ON [Stk - Stock items and Suppliers].CASID = [CS - Client and Suppliers].CASID WHERE ((([PO - Stock items ordered].POID)=[forms]![OrderInfo]![POID]) AND (([Stk - Stock items and Suppliers].StkItemID)=[forms]![OrderInfo]![ODC5Code])); 
Now this is what I am after. First, replace any single quotes with doubles. Now test the length of the string and if it is greater than 1000 characters then find the last comma before the 1000 characters, from that comma splt from there on into the next line of the textbox2. Then it would have to loop the 2nd line checking for 1000 characters etc.... Once it has finished with that then it will add single quotes to each line of the result and " & _ to the end of each line. Then for the very last line in the textbox it replaces " & _ with ; " I am not opposed to this being processed in a spreadsheet, I can do most of it but not the splitting each line at the last comma before 1000 characters.

This question generated 16 answers. To proceed to the answers, click here.

This thread is current as of April 30, 2015.

For more resources for Microsoft Excel