vba access splitting a long string into complete words

**Susy** · 03-13-2017, 02:34 AM

Hi!

Unfortunately the "why" I cannot answer. It is a requirement.

The function above is quite good, the only problem is the function nextspace... as I already explained above.

I found the splitTo40 code in an excel forum, and then I modified so it fits my problem. I added the
1. myRange (array) with 35,40,40,100

2. had an external elements to the loop...

Code:

Set ws = Sheets("Sheet1")
    LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
        
    r = 1
    i = 3
    str_Out = " "
           
    myRange = Array(35, 40, 40, 100)
    
    For r = 2 To LastRow + 1
    
    strString = (Split(Trim(ws.Cells(r, 1).Value)))
       
        For iloop = LBound(strString) To UBound(strString)
            
            If (Len(Trim(str_Out)) + Len(Trim(strString(iloop)))) >= myRange(i - 3) Then  

                ws.Cells(r, i).Value = Trim(str_Out)
                i = i + 1
                str_Out = ""
            End If
            
            str_Out = str_Out & strString(iloop) & " "
        
        Next
        
        
        ws.Cells(r, i).Value = Trim(str_Out)
        i = 3
        str_Out = " "

The idea is now to use the code from Andy49 ( I love the breaks approach!) and modified so it includes the array with the set of number of characters (35, 40, 40, 100). The nextspace function is faulty.

Ideas?

**Susy** · 03-13-2017, 04:52 AM

As Andy49 recommended, I expanded his function like this (see below). Now the issue with the characters being of different length is solved... BUT

Code:

Option Compare Database
Global break(4) As Integer
Option Explicit


Public Function splitter(textline As String, ct As Integer, length As Integer) As String


    break(ct) = nextspace(textline, break(ct - 1), length)


    splitter = Mid(textline, break(ct - 1) + 1, break(ct) - break(ct - 1))
    'Mid ( text, start_position, number_of_characters )


End Function


Public Function nextspace(textstring As String, i As Integer, length As Integer) As Integer


    nextspace = InStr(i + length, textstring, " ")
    'InStr ( [start], string_being_searched, string2, [compare] )


    If nextspace = 0 Then
        nextspace = Len(textstring)
    End If


End Function

BUT, the problem is the "nextspace" function.... instead of going to the next space, it should stop at the step before!

Ideas?

**andy49** · 03-13-2017, 05:02 AM

Can you clarify "it should stop at the step before" please

Sent from my iPhone using Tapatalk

**Susy** · 03-13-2017, 05:49 AM

I mean, if the string should be max 35 characters, then it should be 35 and no more...

The nextspace function finds the first "space" after founding the 35th character... which means it also includes the word after the 35th character.

**Susy** · 03-13-2017, 09:37 AM

I got no idea :-(

**orange** · 03-13-2017, 09:43 AM

See my code sample post 7 -- InstrRev

**andy49** · 03-13-2017, 10:31 AM

Code:

Function Splitter(txt As String, group As Integer) As String
 
 
Dim myrange(4)
Dim r As Integer
Dim i As Integer
Dim str_out As String
Dim strstring() As String
Dim iloop As Integer
    
    i = 3
    str_out = " "
           
    myrange(0) = 35
    myrange(1) = 40
    myrange(2) = 40
    myrange(3) = 100
    
    
    strstring = Split(Trim(txt))
    
    
    
     
        For iloop = LBound(strstring) To UBound(strstring)
            
            If (Len(Trim(str_out)) + Len(Trim(strstring(iloop)))) >= myrange(i - 3) Then
              If i - 2 = group Then
              Splitter = str_out
                
                
                
                Exit Function
                End If
                
                
                i = i + 1
                str_out = ""
            End If
          
            str_out = str_out & strstring(iloop) & " "
        
        Next
        
        
      
        i = 3
        str_out = " "
        
       
End Function

Code:

SELECT Table1.stringtosearch, Splitter([Table1]![stringtosearch],1) AS expr1, Splitter([Table1]![stringtosearch],2) AS expr2, Splitter([Table1]![stringtosearch],3) AS expr3, Splitter([Table1]![stringtosearch],4) AS expr4
FROM Table1;

Feel free to test the above code and function

**Susy** · 03-13-2017, 10:32 AM

Thanks,

the problem is that the above nextspace function only gives an integer and not a string... and InstrRev needs a string to do the comparison or search!

**Susy** · 03-13-2017, 12:14 PM

Almost perfect... somehow after the i = 4, the counter does not go up!.... I don't get it ;-)

I had to move the counter to another position, otherwise it was executing the same order.... 35 characters --> myrange(0)

Expression 4 is not being started at all...

Code:

Function Splitter(txt As String, group As Integer) As String
 
 
Dim myrange As Variant
Dim r As Integer
Dim i As Integer
Dim str_out As String
Dim strstring() As String
Dim iloop As Integer
    
    i = 3
    str_out = ""
  
    myrange = Array(35, 40, 40, 100)
    
    
    strstring = Split(Trim(txt))
    
        For iloop = LBound(strstring) To UBound(strstring)
            
            If (Len(Trim(str_out)) + Len(Trim(strstring(iloop)))) >= myrange(i - 3) Then
              If i - 2 = group Then
              
              
              Splitter = Trim(str_out)
                
              
                
                'Exit Function
                End If
                
                 
                i = i + 1
                str_out = ""
                
                
            End If
          
            str_out = Replace(str_out, "  ", " ") & strstring(iloop) & " "
        
        Next iloop
        
        
      
        i = 3
        str_out = " "
        
       
End Function

this is really great.... almost there,

thanks andy49!

**andy49** · 03-13-2017, 01:44 PM

Try this function

same query

Code:

Function Splitter(txt As String, group As Integer) As String
  
  
 Dim myrange(4)
 Dim r As Integer
 Dim i As Integer
 Dim str_out As String
 Dim strstring() As String
 Dim iloop As Integer
     
     i = 3
     str_out = " "
            
     myrange(0) = 35
     myrange(1) = 40
     myrange(2) = 40
     myrange(3) = 100
     
     
     strstring = Split(Trim(txt))
     
     

      
         For iloop = LBound(strstring) To UBound(strstring)
               
             If (Len(Trim(str_out)) + Len(Trim(strstring(iloop)))) >= myrange(i - 3) Then
               i = i + 1
               If i - 3 = group Then
               Splitter = str_out
                 
                
                 
                 Exit Function
                 
                 Else: str_out = ""
                 End If
                 
                 
                 
                
             End If
           
             str_out = str_out & strstring(iloop) & " "
         If i = 6 Then Splitter = str_out
         Next
         
         
       
    
         
        
 End Function

It has a hitch if the last characters take you past the hundred mark

Let me know

**Susy** · 03-13-2017, 02:50 PM

I just tested the code and... this is the real deal

It is working great. I will keep testing it tomorrow.

Thanks Andy49

**Susy** · 05-18-2017, 10:04 AM

Hi Andy and every one there!

I just found out that the function Splitter is almost perfect...

Take for instance this sentence...
Iteration of lenght{ 35, 40, 40, 100}
"Today is a beatiful day, since it is sunny."

Iteration 0 (35) = Today is a beatiful day, since it
Iteration 1 (40) = is sunny.

In the second interation (1), the splitter function does not deliver the words "is sunny"
It doesn't always have to be bigger than 40 characters...

Code:

If (Len(Trim(str_out)) + Len(Trim(strstring(iloop)))) >= myrange(i - 3) Then               
               i = i + 1
               If i - 3 = group Then 
                       Splitter = str_out

Here, Splitter = str_out --- delivers the string... but

if it is smaller than the values in the range {35, 40, 40, 100} it does not do anything... which is wrong.

Code:

        Else
              str_out = ""
        End if
   End if

The splitter function is ignored when the string is smaller then defined lenght {35, 40, 40, 100} which it cannot happen.

Code:

If (Len(Trim(str_out)) + Len(Trim(words(iloop)))) >= myRange(i - 3) Then

How to include those cases when it is smaller?

--I have been experimenting for a while, but unsuccess :-(

Regards,

**Susy** · 05-18-2017, 11:49 AM

Even if the string is smaller than its specification {35,40,40,100} in each iteration/group... it should be included... which is not case now... :-(

Any ideas before starting from scrap again...

Cheers

**neenajk** · 10-03-2022, 01:46 AM

You can also directly import CSV into table using SSMS in SQL Server or any other tool

**Gicu** · 10-03-2022, 11:06 AM

Thanks, but you're replying to a 5 year old thread, I see you're a new member, maybe wait for some newer threads....

Cheers,

vba access splitting a long string into complete words

Thread Tools

Similar Threads

Access Error - String returned by builder was too long - the results will be truncated.

Splitting the Database has lead to very long delays.

Swap words from a string.

Splitting long string to multiple fixed-length strings? Possible w/query?

How to get matching words in string of characters.

Posting Permissions