Trimming strings in a line or per line

JohnDBCTX
NewLounger
Posts: 12
Joined: 18 Jun 2016, 06:14

Trimming strings in a line or per line

Post by JohnDBCTX »

I have some issues with trimming strings, but what if you have the following file.

In "Another List of Names.csv"

Male Names: Dave, Jack, John Female Names: Cindy, Mary, Pam


What I want is the following:


In Text Number One.csv:

Male Names: Dave, Jack, John


And in Text Number Two.csv

Female Names: Cindy, Mary, Pam


So far, I have the following code snippet:

Code: Select all

Sub AllEntriesToDifferentFiles()
 Dim Xp, NEntry As Long
 Dim strZ, strM, strF, strCropM, strCropF, strTrimStr, strEntrireLine As String



 Open "C:\Users\John\Documents\Another List of Names.csv" For Input As #1
 Open "C:\Users\John\Documents\Text Number One.csv" For Output As #3
 Open "C:\Users\John\Documents\Text Number Two.csv" For Output As #4


strM = Trim("Male Names")
 strF = Trim("Female Names")
 strCropM = Left(strM, 25)
 strCropF = Left(strF, 25)


While Not EOF(1)
     Line Input #1, strZ
     If InStr(1, strZ, strM, vbBinaryCompare) Then
         Write #3, strM
     End If
     If InStr(1, strZ, strF, vbBinaryCompare) Then
         Write #4, strF
     End If
 Wend


Close #4, 3, 1



 End Sub

So far, it has given the output:

In Text Number One.csv:

Male Names


And in Text Number Two.csv:

Female Names


How could I improve this source code snippet, so that it can produce the output that I want?

Regards,

JohnDBCTX

User avatar
HansV
Administrator
Posts: 69430
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Trimming strings in a line or per line

Post by HansV »

Is the text

Male Names: Dave, Jack, John Female Names: Cindy, Mary, Pam

all in one line? And if so, will it always be in this format, i.e. first the literal text "Male Names:", then some names, then the literal text "Female Names", and finally some names again?
Regards,
Hans

User avatar
HansV
Administrator
Posts: 69430
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Trimming strings in a line or per line

Post by HansV »

Remark: the line

Dim strZ, strM, strF, strCropM, strCropF, strTrimStr, strEntrireLine As String

declares only strEntrireLine as a String, and all the other variables mentioned as Variant. In VBA, you must specify the data type of each variable separately:

Dim strZ As String, strM As String, strF As String, ...
Regards,
Hans

User avatar
HansV
Administrator
Posts: 69430
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Trimming strings in a line or per line

Post by HansV »

See if the code below does what you want. Note that there is no point in trimming strM and strF, nor taking the first 25 characters of these strings: they are fixed strings. You might even declare them as constants.
To extract the male and female names, we have to look at the position of "Male Names" and "Female Names" in each line.

Code: Select all

Sub AllEntriesToDifferentFiles()
    Const strM = "Male Names:"
    Const strF = "Female Names:"

    Dim strZ As String
    Dim lngPosM As Long
    Dim lngPosF As Long

    Open "C:\Users\John\Documents\Another List of Names.csv" For Input As #1
    Open "C:\Users\John\Documents\Text Number One.csv" For Output As #3
    Open "C:\Users\John\Documents\Text Number Two.csv" For Output As #4

    Do While Not EOF(1)
        Line Input #1, strZ
        lngPosM = InStr(strZ, strM) ' by default case sensitive
        lngPosF = InStr(strZ, strF)
        If lngPosM > 0 Then
            ' We have male names
            If lngPosF > lngPosM Then
                ' We have female names after the male names
                Write #3, Trim(Mid(strZ, lngPosM, lngPosF - lngPosM - 1))
            Else
                ' We have no female names after the male names
                Write #3, Mid(strZ, lngPosM)
            End If
        End If
        If lngPosF > 0 Then
            ' We have female names
            If lngPosM > lngPosF Then
                ' We have male names after the female names
                Write #4, Trim(Mid(strZ, lngPosF, lngPosM - lngPosF - 1))
            Else
                ' We have no male names after the female names
                Write #4, Mid(strZ, lngPosF)
            End If
        End If
    Loop

    Close #4, #3, #1
End Sub
Regards,
Hans

JohnDBCTX
NewLounger
Posts: 12
Joined: 18 Jun 2016, 06:14

Re: Trimming strings in a line or per line

Post by JohnDBCTX »

Here I have improved the code snippet:

Code: Select all

Sub AllEntriesToDifferentFiles()
Dim strZ As String, strM As String, strF As String, strCropM As String, strCropF As String


Open "C:\Users\John\Documents\Another List of Names.csv" For Input As #1
Open "C:\Users\John\Documents\Text Number One.csv" For Output As #3
Open "C:\Users\John\Documents\Text Number Two.csv" For Output As #4


While Not EOF(1)
    Line Input #1, strZ
        strCropM = Left(strZ, 29)
        strCropF = Right(strZ, 30)
    If InStr(1, strZ, "Male Names:", vbBinaryCompare) Then
        Write #3, strCropM
    End If
    If InStr(1, strZ, "Female Names:", vbBinaryCompare) Then
        Write #4, strCropF
    End If
Wend

Close #4, 3, 1


End Sub
But that might not be enough. If I am supposed to search the text from left to right per line, how am I supposed to perform that task?

Code: Select all

If [text within search] = "Male Names:" 
then output in Text Number One.txt Male Names: 
Until cursor reaches the text = "Female Names",  and vice versa.
In this case it is supposed to be commented.

Regards,

JohnDBCTX

JohnDBCTX
NewLounger
Posts: 12
Joined: 18 Jun 2016, 06:14

Re: Trimming strings in a line or per line

Post by JohnDBCTX »

Thanks, it worked even better.

I did not know until now I could perform the following:
1. Declare them as constants, which are fixed as I know.
2. Declare longPosM and longPosF as long data types just to make it case sensitive
3. Perform a Do while...Loop that performs the conditional statements.

I have printed the code snippet, so that I can use it for reference.


Regards,

JohnDBCTX

User avatar
HansV
Administrator
Posts: 69430
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Trimming strings in a line or per line

Post by HansV »

JohnDBCTX wrote:2. Declare longPosM and longPosF as long data types just to make it case sensitive
No, that isn't correct. I used the variables lngPosM and lngPosF to store the position of strM and strF within the line you're investigating.
The InStr function can be used in two ways:

InStr(start, search_in, search_for, option)

and

InStr(search_in, search_for, option)

In the second version, start = 1 is assumed. In both versions, option can be used to specify that the search is case sensitive (vbBinaryCompare) or case insensitive (vbTextCompare). If you omit option, the search will be case sensitive unless you have specified explicitly that ALL text comparisons must be case insensitive. So

InStr(search_in, search_for)

is case sensitive by default.
Regards,
Hans