Breaking One Field Into Three During Export

[shewolf]

Splitting / Breaking One Field During Export

I have a bit a a quandry. I'm trying to figure out a way to Export some information from a Database into Excel so I can load it into a label printing program. My difficulty results from my need to break the "FileTitle" field, which can have up to 255 characters, into three fields that can be no longer than 28 characters.

I know I can do a simple export into Excel and then use convert text to columns but that breaks up words and I want the break to occur at blank spaces, so no words are broken up.

I'm hoping to find some way to tell the system to look back a certain amount of characters, from the potential breaking point, and tell it to break where it sees a blank space character and nothing else.

I'm trying to avoid having to retype all the titles into the label program.

Any and all assistance will be greatly appreciated.

 

[Kicker]

You have to work backwards from a known length. In your case 28 characters. I would create a function that would get the first character space before the 29th character. Example:

dim export_string as string
dim n as long
n = 29
do while mid(str_field, n,1) <> " "
     n = n - 1
loop
export_string = mid(str_field, 1, n)

You get the idea. then you can either go from n + 28 and so forth until you have all of the segments needed.

Hope this helps

ttfn
Kicker

 

[shewolf]

Still Confused

Kicker,

Thanks so much, now I have hope that what I want to do *can* be done but I am still confused.

I know I must be missing something really obvious, but where do I put it or how do I tell my export macro to run such code before it puts it into the Excel spreadsheet.

Right now I've got a query that I can run to select files added since a given date. The query then displays the relevant fields that I need to print on my new labels. I have a macro set up to automatically run that query and update a file on a server.

Do I need to have such a command set up to run as part of the query and then export it?

I am afraid I know just enough to know what's possible but not, as Captain Picard would say, to "make it so".

Charis

 

[Kicker]

Ok..The following goes into a module which is added using vba.

Option Explicit

Public Function getString(ByVal strlong As String) As String
Dim n As Long

    For n = 29 To 1 Step -1
        If Mid(strlong, n, 1) = " " Then
            getString = Mid(strlong, 1, n)
            Exit For
        End If
    Next n

End Function

Public Sub splitString()
Dim n As Long
Dim strTest As String
    
    strTest = Range("A1").Value
    'Of course you will want to do some testing of the length of
    'the string before you try testing..use something like
    'if len(strtest) >=29 then
    For n = 29 To 1 Step -1
        If Mid(strTest, n, 1) = " " Then
            Range("A5").Value = Mid(strTest, 1, n)
            Exit For
        End If
    Next n
    
    strTest = Mid(strTest, n + 1)
    For n = 29 To 1 Step -1
        If Mid(strTest, n, 1) = " " Then
            Range("A6").Value = Mid(strTest, 1, n)
            Exit For
        End If
    Next n
End Sub

This assumes you have a long string in cell A1. the function is entered into a cell as [COLOR=Blue[B]]= getstring[/B](A1)[/COLOR]

To run the sub, run it as a macro from Alt-F8

These are just examples and need to be modified, but you should be able to get the idea working.

ttfn
Kicker

 

[shewolf]

Getting Closer but not there yet

I've managed to piece together a partial solution. The code is not pretty but it works. But now the problem I'm running into is that I get an error message when the code runs on strings that are NOT long enough to require the first or second splits.

How do I handle those?

Thanks so much for your help.

Charis

***************

Public Function reduceit(strIn As String) As String
Dim export_string As String
Dim n As Long
n = 29
Do While Mid(strIn, n, 1) <> " "
n = n - 1
Loop
export_string = Mid(strIn, 1, n)
reduceit = export_string
End Function


Public Function secondline(strIn As String) As String
Dim reducit As String
Dim n As Long
n = 29
Do While Mid(strIn, n, 1) <> " "
n = n - 1
Loop
reducit = Mid(strIn, 1, n)
Dim A As String
A = Len(reducit)
Dim export_string As String
Dim X As Integer
X = InStr(A, strIn, " ")
Dim almost As String
Dim Z As Integer
almost = Mid(strIn, X, 34)
Z = InStrRev(almost, " ", -1)
Do While Mid(almost, Z, 1) <> " "
Z = Z - 1
Loop
export_string = Mid(almost, 1, Z)
secondline = export_string
End Function
Public Function thirdline(strIn As String) As String
Dim reducit As String
Dim n As Long
n = 29
Do While Mid(strIn, n, 1) <> " "
n = n - 1
Loop
reducit = Mid(strIn, 1, n)
Dim A As String
A = Len(reducit)
Dim export_string As String
Dim X As Integer
X = InStr(A, strIn, " ")
Dim almost As String
Dim Z As Integer
almost = Mid(strIn, X, 34)
Z = InStrRev(almost, " ", -1)
Do While Mid(almost, Z, 1) <> " "
Z = Z - 1
Loop
Dim notyet As String
notyet = Mid(almost, 1, Z)
Dim B As Integer
B = Len(notyet)
Dim C As Integer
C = (A + B)
Dim V As Integer
V = InStr(C, strIn, " ")
Dim W As Integer
Dim gettingthere As String
gettingthere = Mid(strIn, V)
Dim T As Integer
T = InStrRev(gettingthere, " ", -1)
Do While Mid(gettingthere, T, 1) <> " "
T = T - 1
Loop
export_string = Mid(gettingthere, 1, T)
thirdline = export_string
End Function

 

[Kicker]

the examples I gave you work and are simple to use. ALL of the examples would use an if statement for length.

if len(stringName) > 29 then
run the code for the first segment
endif

if len(stringName) > n+29 then
run the code for the second segment
endif

and so forth

 

[gMan2020]

Additional Issue

Hello,

I have a problem very similar to this...the problem is that I have a string that must be broken up into fields no larger than 70. The additional issue here is that each record has an identifier #, a sequence number, and this string. So it looks like this: 12345678, 1, [string].

I need to break up the string, keep the identifier # with each new line, and re-sequence the sequence number so for example, if the string is 210 characters long the 3 new records would look like this:

12345678, 1, string
12345678, 2, string
12345678, 3, string

After this is finished the process would repeat with a new identifier number, sequencing, and string.

I see where the code provided by kicker would go but I can't figure out how to add the additional method of keeping the identifier and sequence numbers and the new records are created. Thanks.

 

[Kicker]

Just build 3 variables. For example:

strVar1 = "12345678, "
strVar2 = str 'n would be a "counting" variable in a for next statement
strVar3 = use the above code to create this string.

Then when all done, back to the regular concatenated string such as

strVar1 & strVar2 & strVar3

would give the result
12345678, 1, first 70 characters

hope this works

ttfn
Kicker

 

[gMan2020]

Just to make sure...since I have several records with these long strings of notes, will the code go to the next record and repeat the process? Like

12345678, 1, string
12345678, 2, string
12345678, 3, string
98765432, 1, string
98765432, 2, string

Sorry, I'm really having trouble with this.

 

[Kicker]

You are going to have to nest several for next loops.

for varNum = 1 to totNumStrings
     strVar1 = "12345678, "
     str_field = mid(str_field, 12)
     for i = 1 to 3  '?
        strVar2 = str(i)
        n = 70
        do while mid(str_field, n,1) <> " "
             n = n - 1
        loop
        export_string = strVar1 & strVar2 & mid(str_field, 1, n)
     next i
next varNum

errgo, the first for next goes through all the strings
the second for next goes through the smaller number of strings
the third goes through the string itself for the individual parts

ttfn
Kicker