Copying Characters to the Right of a Selected String in a .txt file

pdotrun · July 9th, 2011, 10:20 PM

I am trying to make a VBA macro in Excel that searches for 5 characters to the right of a selected string in a .txt file and pastes it in the selected cell of choice. Can anyone help?

thanks

Shasur · July 9th, 2011, 11:02 PM

There are many ways to read content from TextFiles

Here you have a few of them

http://vbadud.blogspot.com/2007/06/v...h-leading.html

After you read the text to a string you can take the rightmost five characters using the Right function

right(MyString,5)

Cheers
Shasur

pdotrun · July 10th, 2011, 03:38 PM

thanks for the help, can wait to get to it tonight!

pdotrun · July 12th, 2011, 09:46 PM

So i guess i didn't explain the problem well enough...

I have a few text files that have the same format but different latitude, longitude and elevation values within them. Because the values are different in every text file i wanted to search for the text that is constant and copy the characters to the right of that constants.

e.g. sample of text = "latitude 123 23 53"
string = "latitude "
character that i want = "123 23 53"

paste the latitude value in cell A1
paste the longitude value in cell B1

-thank

Shasur · July 12th, 2011, 09:52 PM

If you have a clean set of entries you can use the following:

Code:

Sub SplitLat()
Dim sMixed As String
Dim sOutput As String
Dim sFind As String
sMixed = "latitude 123 23 53"
sFind = "latitude"
' Replace method
If InStr(sMixed, sFind) <> 0 Then ' If the text is found
    sOutput = Trim(Replace(sMixed, sFind, "", , , vbTextCompare))
End If
End Sub

Cheers
Shasur