Should I use Trim or Len?

[swan717]

I've copied and pasted some information (3 columns) from a pdf file into Excel. I pasted the information and everything appeared in one column. So I used the Convert Text To Columns - Delimited - by a period (as the text has periods to separate the three columns. However, some of the information from the third column appears in the second column. The information in the Third Column is not important and can be deleted.
Can I use the trim or len functions of VB to do this and if so how?

Thanks!

 

[MelissaEvans]

Trim removes spaces in the front and end of your string, Len returns how many characters are in the string. I think you'd have to do some parsing on your own using InStr and Mid. Something like this maybe...

Dim DataStr as String  ' assumes has 3 cols, 2 periods
Dim PeriodPos as Integer
Dim NewStr as String

PeriodPos = InStr(1, DataStr, ".") ' the first period
PeriodPos = InStr(1, DataStr, ".") ' the second period
NewStr = Mid(DataStr, 1, Len(DataStr) - PeriodPos)

This is probably off by one character this way or that, you'd have to tweek it some.

Mid's parameters are: Mid(string to use, where to start, how far to go)
InStr's parameters are: InStr(where to start, string to look through, string to look for)

Hope that helps =)

 

[MichaelRed]

Hmmmmm,

Not being a big fan of excel, I wouldn't go there. If you just put the 'whole thing' into vb - or even just a plain old text file, you could use the built in function Split with the dot as the delimiter.

something along:

[tab]Dim MyWords as Variant

[tab]MyFil = FreeFile
[tab]OPen "SomeTextFileName.Txt" for Input as #MyFil

[tab]Do While Not Eof(MyFil)
[tab][tab]Line Input #MyFil, MyLine

[tab][tab]MyWords = Split(MyLine, ".&quot

[tab][tab][tab]With rst
[tab][tab][tab][tab].AppendNew
[tab][tab][tab][tab][tab]'each delimited string is now an element of the array MyWords, so place it in the proper Field of the recordset
[tab][tab][tab][tab][tab].Field0 = MyWords(0)
[tab][tab][tab][tab][tab].Field1 = MyWords(1)
[tab][tab][tab][tab][tab].Field2 = MyWords(2)
[tab][tab][tab][tab].Update
[tab][tab]End With

[tab]Loop

This is somewhat incomplete, and it does depend on the delimiter being consistient (As the "Dot(.)&quot which may be the reason the excel thing didn't work.

Depending on where the info is located, you will need to loop through the source in some alternate process, getting each "line" of input.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over