Parse a string

[MelB]

I have a field in my database that contains text and is 180 characters. I need to parse it into 3 new fields no greater than 70 characters each and I must divide the original text on the spaces between words. I know I have seen this done but can't find it anyplace. I would appreciate any help or links to examples of how to do this.... Thanks in advance

 

[DJkarl]

I have a field in my database that contains text and is 180 characters. I need to parse it into 3 new fields no greater than 70 characters each and I must divide the original text on the spaces between words. I know I have seen this done but can't find it anyplace. I would appreciate any help or links to examples of how to do this.... Thanks in advance

For parsing up the string into 70 character chunks I'd probably use the Mid function.

For dividing the text in the smaller strings based on spaces I'd use the Split function.
I.E.
Sub ParseString()
Const tstSTR As String = "A B C D"
Dim strAR As Variant

strAR = Split(tstSTR, Chr(32))

'You'd get an array with the following data
'strAR(0) = "A"
'strAR(1) = "B"
'strAR(2) = "C"
'strAR(3) = "D"

End Sub

 

[Moniker]

I don't think he wanted each space to be a separator. What he's after is a string parser that goes to a maximum of 70 characters, and it will go to the space before the 70 character mark if splitting at the 70 character mark would split a word in half.

In that spirit, look at the attached file. I used this sentence as my test:

This is a test of the emergency broadcasting system. If this were a real emergency, you'd probably already know about it.

I've defaulted the Max. Field Length to 70, but you can set it to whatever you want. To see how it works, open the form f_Parsing, paste in your own sentence of 70 or more characters, and click start. Open the debug window (press Ctrl-G) and in the immediate window at the bottom, you'll see your sentence parsed out for you. The code behind the "Start" button on the form shows how I did it.

There may be other ways to do it, but that one took maybe three minutes to write and test and it works. You can easily adapt it to write to fields in a table instead of the debug window.