vba access splitting a long string into complete words

[Susy]

Hi there,

I am trying to import data (.csv) into a table. :-) this part is done!

While importing the data, split content of some fields into 1-4 extra fields containing part of string... :-( this part is not done.. The function is missing!

What I am looking for is the following:

*-- if the field "Company Name" is very big ( 35<Company Name<150)... then
split the name into complete words like this
Company Name1 --> 35 characters
Company Name2 --> 40 characters
Company Name3 --> 40 characters
Company Name 4 --> all remaining characters...

If any of you has stumbled upon a similar function or code, please let me know...

Regards

[andy49]

function splitfn (textstring as string) as variant
Dim arr(4) As variant
splitfn = left(textstring,30) & "^" & mid(textstring,31,40) & "^" & mid(textstring,72,40) & "^" & mid(textstring,113,40)
arr = split(splitfn,"^")
splitfn = arr
end function

Something like the above?

[Susy]

Thanks, but how can I read the array?

I also get an error: Compile error: Can't assign to array.

:-(

Does your code give complete words, I don't think so, right?

[orange]

Here is a sample of using the Split() function in Access vba.
It may help you with the long names.

'---------------------------------------------------------------------------------------
' Procedure : splitname
' Author    : mellon
' Date      : 03-Mar-2017
' Purpose   :To show use of Split to separate a long string into
' parts where each part name is separated with " "
'---------------------------------------------------------------------------------------
'
Sub splitname()
10        Dim lngstr As String: lngstr = "the Very Long Company Name with Many Parts"
          Dim x As Variant, i As Integer
20        On Error GoTo splitname_Error

30        x = Split(lngstr, " ")
40        For i = LBound(x) To UBound(x)
50            Debug.Print i; x(i)
60        Next i
70        On Error GoTo 0
80        Exit Sub

splitname_Error:

90        MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure splitname of Module AWF_Related"

End Sub

Which gives this result:

 0 the
 1 Very
 2 Long
 3 Company
 4 Name
 5 with
 6 Many
 7 Parts

Good luck.

[Susy]

Thanks, but I don't need single words... but complete words up to 40 characters, 35 characters, 35, characters, remaining characters...

[aytee111]

In the query, it would be
CompanyName1: Left(CompanyName,35)
CompanyName2:Mid(CompanyName,36,40)
etc

I don't think it would be a good idea to try and make the number of fields variable - always have the 4.

Edit - this won't give complete words! You will have to use Orange's method and concatenate the fields but not go over the limit.

[orange]

Here is another procedure that should do what need.
I have mocked up your issue with a long string to simulate your long title/name.

'---------------------------------------------------------------------------------------
' Procedure : splitnameMar2017
' Author    : mellon
' Date      : 03-Mar-2017
' Purpose   : To take a large string and break it into pieces of full words where
' each piece is 40 chars or less
'
' In response to post
'   https://www.accessforums.net/showthread.php?t=64772&p=349437#post349437
'
'---------------------------------------------------------------------------------------
'
Sub splitnameMar2017()
10  On Error GoTo splitnameMar2017_Error

20  Dim lngstr As String
30  lngstr = "Testimony to the Extraterrestrial Alderbranch Economics, Galactic Adventureworks " _
             & " and Oliogenous Nutraceuticals Conference of 2017 thru 2019"

    Dim s As String, Loc As Integer
    Dim swork As String
40  s = lngstr
50  Do While Len(s) > 3
60      swork = Left(s, 40)

'line 70 gets the LOCation of the first space working from position 40 (end of this 40 chars) back toward the start.
'this ensure you get "full  words"

70      Loc = InStrRev(swork, " ")
80
90      If Len(s) < 40 Then
100         Debug.Print LTrim(s)
110         Exit Do
120     Else
130         Debug.Print Mid(LTrim(swork), 1, Loc - 1)
140         s = Mid(LTrim(s), Loc)
150     End If
160 Loop
170 On Error GoTo 0
180 Exit Sub


190 On Error GoTo 0
200 Exit Sub

splitnameMar2017_Error:

210 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure splitnameMar2017 of Module AWF_Related"

End Sub

Here is the output from the routine:

Testimony to the Extraterrestrial
Alderbranch Economics, Galactic
Adventureworks and Oliogenous
Nutraceuticals Conference of 2017 thru
2019

Good luck with your project.

[andy49]

Public Function splitter(textline As String) As String
Dim ct As Integer
Dim break(4) As Integer
For ct = 1 To 4
break(ct) = nextspace(textline, break(ct - 1))
splitter = Mid(textline, break(ct - 1) + 1, break(ct) - break(ct - 1))

Next
End Function

Public Function nextspace(textstring As String, i As Integer) As Integer
nextspace = InStr(i + 40, textstring, " ")
If nextspace = 0 Then nextspace = Len(textstring)

End Function

Not sure if this helps but it breaks the text down. Maybe one of the others could help link it to a query function

[Susy]

Thanks Andy49... I will check it now.

[Susy]

This is the field "Company_Name" from the .csv file I would like to import into MS Access:

A very long name which I need to split into 1, 2, 3 or 4 different  fields depending on the   number of characters, because the 1, 2, 3 and 4  fields should be 35, 40, 40,   100 characters long!

What I am looking for is to split (full words) this long "Company Name" into four company fields (Company_Name 1, Company_Name 2, Company_Name 3, Company_Name 4) of lenght (35, 40, 40, 100) respectively when importing the .csv file into ms access in the table "tbl_Revenue"....

Field 1 in table in Access: Company_Name 1 (length = equal or less than 35 characters)

A very long name which I need to

Field 2 in table in Access: Company_Name 2 (length = equal or less than 40 characters)

split into 1, 2, 3 or 4 different fields

Field 3 in table in Access: Company_Name 3 (length = equal or less than 40 characters)

depending on the number of characters,

Field 4 in table in Access: Company_Name 4 (length = equal or less than 100 charachters)

because the 1, 2, 3 and 4 fields should be 35, 40, 40, 100 characters long!

Some function or procedure which could help me to do this will be highly appreciated! Even tips!

[andy49]

OK my next version:

Option Compare Database
Global break(4) As Integer
Option Explicit
Public Function splitter(textline As String, ct As Integer) As String

break(ct) = nextspace(textline, break(ct - 1))
splitter = Mid(textline, break(ct - 1) + 1, break(ct) - break(ct - 1))

End Function
Public Function nextspace(textstring As String, i As Integer) As Integer
nextspace = InStr(i + 40, textstring, " ")
If nextspace = 0 Then nextspace = Len(textstring)
End Function

then a query whos SQL is.............

SELECT Table1.companyname, splitter([companyname],1) AS split1, splitter([companyname],2) AS split2, splitter([companyname],3) AS split3, splitter([companyname],4) AS split4
FROM Table1;

When running this query either as a select query or as a maketable query you generates the pic at the top

[Susy]

thanks, the idea is very good, still "nextspace" moves to the next space after counting 35... (I changed the code from 40 to 35).
split 1 <= 35 characters (full words)
split 2 <=40 characters (full words)
split 3 <=40 characters (full words)
split 4 <=100 characters (full words)

[andy49]

Ok change the function slightly

ct as integer ,length as integer

Then change the value 40 into the word length.

And the query altered to include the extra input

[companyname],1,35 as split 1
[companyname],2,40 as split 2

And so on.

Apologies for rough explanation. No pc here



Sent from my iPhone using Tapatalk

[Susy]

Thank you very much...

I will test it on Monday...

For now, enjoy your well deserve weekend!

[orange]

Susy,

Please explain why the fragments you need are different lengths--in simple terms.
The sample I posted (post 7) will break on a space as close as possible to 40 characters.

I'm trying to visualize a business set up for splitting the original string into varying sized pieces, and haven't found one.