Help with TRIM Function

[Willie T]

I was wondering if anyone could help me with a TRIM Function listed
below. I'm a beginner programmer and Very Green...

I'm reading several text files then writing data to an Excel WS. All
works well except for the output of PID listed below.

The string it the text file is as follows:
Report on Parcel :15-24-2-000-022.000 00Generated :1/4/2005
Note that there are 7 leading blank spaces

StrPID = LCase("Report on Parcel :") '(7 Leading Blank Char)

Therefore; then output is as follows:
15-24-2-000-022.000 00Generated :1/4/2005

where the desired output would be as follows:

15-24-2-000-022.000 00

can I Trim a line in 2 places to output the desired results.

Thanks for any help in advance

Willie T

Code Listed:
Do While Not EOF(FileNum)
Line Input #FileNum, myLine
If LCase(Left(Trim(myLine), Len(StrPID))) = StrPID Then
wks.Cells(oRow, "A").Value = Trim(Mid(myLine, Len(StrPID) +
7))
FoundPID = True
ElseIf LCase(Left(Trim(myLine), Len(StrAddr))) = StrAddr Then
wks.Cells(oRow, "B").Value = Trim(Mid(myLine, Len(StrAddr) +
19))
FoundAddr = True
ElseIf LCase(Left(Trim(myLine), Len(StrCity))) = StrCity Then
wks.Cells(oRow, "C").Value = Trim(Mid(myLine, Len(StrCity) +
1))
FoundCity = True
ElseIf LCase(Left(Trim(myLine), Len(StrLandValue))) =
StrLandValue Then
wks.Cells(oRow, "D").Value = Trim(Mid(myLine,
Len(StrLandValue) + 19))
FoundLandValue = True
ElseIf LCase(Left(Trim(myLine), Len(StrImpValue))) =
StrImpValue Then
wks.Cells(oRow, "E").Value = Trim(Mid(myLine,
Len(StrImpValue) + 19))
FoundImpValue = True
ElseIf LCase(Left(Trim(myLine), Len(StrTotValue))) =
StrTotValue Then
wks.Cells(oRow, "F").Value = Trim(Mid(myLine,
Len(StrTotValue) + 19))
FoundTotValue = True
Exit Do 'no need to contine reading the file
End If
Loop

 

[Ron de Bruin]

If you have this

Therefore; then output is as follows:
15-24-2-000-022.000 00Generated :1/4/2005

You can use this in a worksheet cell with the string in B1
=MID(B1,1,SEARCH("G",B1,1)-1)

 

[Willie T]

Ron,

Thanks, i can see how in works inside the spreadsheet, but can i add
your statement to my existing TRIM statement so that it will be handled
in my existing VBA code on the fly. In other words combine my TRIM
statement as follows:

If LCase(Left(Trim(myLine), Len(StrPID))) = StrPID Then
wks.Cells(oRow, "A").Value = Trim(Mid(myLine, Len(StrPID) +
7))

with your suggested statement

=MID(B1,1,SEARCH("G",B1,1)-1)
so that the output will be as desired.


Thanks again,

Willie T

 

[Dave Peterson]

See one more response to your other post.

Ron,

Thanks, i can see how in works inside the spreadsheet, but can i add
your statement to my existing TRIM statement so that it will be handled
in my existing VBA code on the fly. In other words combine my TRIM
statement as follows:

If LCase(Left(Trim(myLine), Len(StrPID))) = StrPID Then
wks.Cells(oRow, "A").Value = Trim(Mid(myLine, Len(StrPID) +
7))

with your suggested statement

=MID(B1,1,SEARCH("G",B1,1)-1)
so that the output will be as desired.

Thanks again,

Willie T