Splitting a Field

[Eddie Mason]

I have a table with a text field [Description] that’s 120 characters long; what I need to do is to divide the text in the field into a maximum of 40 character chunks. So the first set of complete words up to 40 characters will be placed in [Desc1], the next complete words up to 40 characters carrying on from the last complete word in [Desc1] to be placed into [Desc2] and the remaining words to be placed into [Desc3]. Can anyone help with this problem?

Kind regard,

Eddie

 

[jdraw]

As an approach
-take a 40 char chunk (1-40) of the field
-check each position for a space starting at 40 and working to 1
For i = 40 to 1 Step -1
if Mid(stringName,i,1) = " " Then
i is the position of the space
your required substring is Mid(stringname,1,i) if you include the trailing space

Once you find the substring (less than 40 chars), then move to the 40 char string starting at i+1 for length 40

You have to keep checking to see how long is the text (stringName). If there are less than 40 chars remaining to be tested, move that remainder to your required String.

Hope this helps.

 

[Eddie Mason]

Hi JDraw,

Thank you for your help, but I'm strugling to see how I move the words to three separate fields?

Kind regards,

Eddie

 

[gemma-the-husky]

why on earth do you need to split the field? i cannot see any point in this.

 

[Eddie Mason]

I need to split the field because I need to export it to computer package that although giving you three fields to enter a description in, each field is only 40 characters in length.

Kind regards,

Eddie Mason

 

[stopher]

Suppose your string is exactly 120 characters long. And suppose there is a convenient space at the 37th character. The next convenient space is exactly 40 characters later.

So you first string will be 36 characters, your second string will be 40 characters. But then how will you deal with your third string that will need to be 44 characters?

Chris

 

[Galaxiom]

JDraw's loop to find the last space in the field can be done in a single line with the InStrRev function.

 

[gemma-the-husky]

eddie - as chris/stopher says

if you split your string into 3, ignoring word boundaries - the simplest code is this (obviously the first could use the left function

strg1 = mid(fullstring,1,40)
strg2 = mid(fullstring,41,40)
strg3 = mid(fullstring,81,40)

however if you try to split the string based on spaces (ie word boundaries) then there is the clear likelihood that the last string will not start at posiiton 81, and is therefore likely to be longer than 40 characters.

 

[Eddie Mason]

Many thanks for your help on this, but it does not appear that this is possible to do. Especially as there are approximately 8000 records that need splitting; 'mid' approach would have to go through each record twice and cut and paste individualy each part completed word into the next field.

kind regards,

Eddie Mason

 

[gemma-the-husky]

what are you trying to do? produce an output file? - you can do that in a single pass with a query. 8000 records will only take a second or two at most, anyway.

 

[jdraw]

JDraw's loop to find the last space in the field can be done in a single line with the InStrRev function.

Thanks Galaxiom, I couldn't remember the name of the function. I was trying StrRev and it wasn't recognized, so used what I was more familiar with.

 

[Eddie Mason]

I agree that I could split a field very quickly using a query, but this would mean that I still had to go through the resulting fields manually. To explain this if the record 1 in field [Description] was: "The Create Wall Mounted WC Pan is a stylish pan that when combined with its elegant cistern will transform a bathroom". The result would be:
[Desc1] "The Create Wall Mounted WC Pan is a sty"
[Desc2] "lish pan that when combined with its ele"
[Desc3] "gant cistern will transform a bathroom"
I would then have to cut and paste "sty" to [Desc2] and "ele" to [Desc3].

Kind regards,

Eddie Mason

 

[gemma-the-husky]

I agree that I could split a field very quickly using a query, but this would mean that I still had to go through the resulting fields manually. To explain this if the record 1 in field [Description] was: "The Create Wall Mounted WC Pan is a stylish pan that when combined with its elegant cistern will transform a bathroom". The result would be:
[Desc1] "The Create Wall Mounted WC Pan is a sty"
[Desc2] "lish pan that when combined with its ele"
[Desc3] "gant cistern will transform a bathroom"
I would then have to cut and paste "sty" to [Desc2] and "ele" to [Desc3].

Kind regards,

Eddie Mason

OK - THe problem remains that if the full phrase is too long, then splitting on word boundaries, might make the third section too long.

What about reworking your app, to let you input 3 phrases of 40 chars each, instead of one long one - then you would not need to tidy the data up at all.

 

[jdraw]

I agree with Dave, some change to input would offer a more efficient process.

But perhaps, you could tell us in very simple terms exactly what the issue is, and why it's an issue. I'm sure you will get a variety of options for solution. You may be making things more difficult by trying to solve an edit/parsing problem that could be eliminated or at least reduced with some adjustments to input data collection.

Just a thought.

 

[Eddie Mason]

Firstly splitting the field will not cause a problem with over-run on the last field as there are no more than 120 characters in any of the 8,000 records. But yes you are absolutely correct it would be so simple to just to adjust the field size in the application to 120 characters and the problem would be resolved, unfortunately the application is a licenced off the shelf product which I have no control over, so my problem still remains.

Kind regards

Eddie Mason

 

[stopher]

Firstly splitting the field will not cause a problem with over-run on the last field as there are no more than 120

You're missing the point. Please read post #6 again.

Here's a functions for you that will do the split:

Public Function getField(inputString As String, FieldNo As Integer, FieldSize As Integer) As String
Dim intBreakPoint As Integer
Dim strW As String
Dim i As Integer

strW = inputString
For i = 1 To FieldNo
    strW = LTrim(strW)
    If Len(strW) > FieldSize Then
        intBreakPoint = InStrRev(Left(strW, FieldSize + 1), " ")
        getField = Left(strW, intBreakPoint - 1)
        strW = Right(strW, Len(strW) - intBreakPoint)
    Else
        getField = strW
    End If
Next i

End Function

So we run the function like this:

getField(input string, number of field we are after, max size of field)

So suppose we test with this (note that the test string is only 116 characters):

strTest = "Lorem ipsum dolor sit ammet, consectetuer adipiscing elit. Maecenas porttitor congue massa. Fusce posuere, magna sed"
Debug.Print getField(strTest, 1, 40)
Debug.Print getField(strTest, 2, 40)
Debug.Print getField(strTest, 3, 40)

the output is:
Lorem ipsum dolor sit ammet,
consectetuer adipiscing elit. Maecenas
porttitor congue massa. Fusce posuere,

So the function I wrote has done the job according to your rules. However, there is still more text to come. Your rules have meant that we can't squeeze the text into 3x40char fields.

Hope you see the that the problem is not with the code but with what you are trying to do.

Chris

 

[Eddie Mason]

Many thanks for your help I'll give the function a try this morning. It is just unfortunate that writers of the software allowed only a maximum of 40 characters in each of three separate fields, rather than one field of 120 characters.

Kind regards,

Eddie Mason

 

[stopher]

I made a slight modification:

Public Function getField(inputString As String, FieldNo As Integer, FieldSize As Integer) As String
Dim intBreakPoint As Integer
Dim strW As String
Dim i As Integer

strW = inputString
For i = 1 To FieldNo
    strW = LTrim(strW)
    If Len(strW) > FieldSize Then
        intBreakPoint = InStrRev(Left(strW, FieldSize + 1), " ")
        getField = Left(strW, intBreakPoint - 1)
        strW = Right(strW, Len(strW) - intBreakPoint)
    Else
        getField = strW
        [COLOR="Red"]strW = ""[/COLOR]    
    End If
Next i

End Function

The code will return the empty string where fields are supposed to be blank. Hope that's ok?

I'd recommend that you also do a test on field 4. That will tell you if the string could not be fitted into the first three fields.

Chris

 

[Eddie Mason]

Many thanks for all your help, I really do appreciate it, it’s now working fine.

Kind regards,

Eddie Mason