Most valuable professional
  • Hot Topics

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. Code: SELECT [PO - Stock items ordered].POID, [Stk - Stock FROM ((((((([PO - Purchase orders] INNER JOIN ([PO - INNER JOIN [Stk - Stock category - Colour] ON [Stk - 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:

This article includes the following tags:

  • Excel
  • Microsoft Excel