Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to find a "word/string" in a text file if I don't use Cells of Excel

How to find a "word/string" in a text file if I don't use Cells of Excel

How to find a "word/string" in a text file if I don't use Cells of Excel

I try to use VBA to read and write a text file without using "Cells". How can I find a "word" in a text file and change this word and output another text file? For example, I want to find all "structural" in the following text file and change it to "nonstructural". Suppose that size of the file is too big to be imported into excel "Cells". Thanks a lot!

1000 temp abc 234 789 789
1000 temp rabc 234 789 673
1000 temp rabc 234 789 5653
1000 temp abc 234 789 343
2000 structural mechanical
3000 static analysis attach
200 mechanical dynamic

RE: How to find a "word/string" in a text file if I don't use Cells of Excel

Read, process, write one line at a time.

Read a line. If it doesn't contain "structural", write it. If it does contain "structural", write the first part, write "non", and write the remainder.

RE: How to find a "word/string" in a text file if I don't use Cells of Excel

Hi Alin 09,

I presume you mean that the file is too large to be read as a single string into a single cell in excel?

Assuming the text file is in distinct lines I would just write some code along the lines of:

Open file

Make a loop that runs until EOF and has a counter i that increments:

Read each line of the file and assign it to a string named "line" & i using the Line Input Statement

Then use the Replace function to change any occurances of "structural" to "non structural" in the string you have just written

Then write the string as line i in a new file.

Loop will end when all lines have been read and changed (if the string was present) and re written to a new file.

save new file.

This would be easier to do in C or C++ if you are familiar with this language as you could search the whole file as a single object. Or indeed, if this is just a one of file cant you just open the file in a text editior and use the find replace function Ctrl H for example?

Cheers Nick

RE: How to find a "word/string" in a text file if I don't use Cells of Excel

Why don't you just open the file in a text editor and use FIND/REPLACE?

RE: How to find a "word/string" in a text file if I don't use Cells of Excel

Everyone, thanks for the reply!
I gave a simplified example. What I really want is to find a word in a line and change another word in the same line.
This example will be more clear:
TEMP 1 100001 56
TEMP 1 100002 56
TEMP 1 100003 34
TEMP 1 100004 67
TEMP 2 100001 56
TEMP 2 100002 26
TEMP 2 100003 34
TEMP 2 100004 67
TEMP 3 100001 56
TEMP 3 100002 26
TEMP 3 100003 34
TEMP 3 100004 67

I want to find the line containing "100001" and change TEMP to "$". I can do this in Text Editor, but it is time consuming since there are a few hundreds to change in one file. Also, it need to be separated to a few files to be read into Excel since there are too many lines. I also need to change the format from Excel output.

I have done the code for test, somewhat like this:

Sub Macro2()
'Dim Data
'Dim Char As String * 1
Open "C:...\Desktop\VBA_read_input.txt" For Input As #1
r = 0
Do Until EOF(1)
Line Input #1, Data
If Mid(Data,9,6) = "#100001" Then
ActiveCell.Offset(r, 0) = "$"
ActiveCell.Offset(r, 0) = Data
End If
r = r + 1
Close #1
End Sub

Here are my questions:
If the file format is not uniform and I don't know the location of the "word/string" in a line. How do I find it? If there any VB function that can search for a "string" in a line?
For example, two lines contains "static"
1000 456 static 00990
1222 static 44556 565676 990

Is it the only way that I need to read by "line" to operate in VBA? Some analysis result files are around 400M and the format is not uniform. How can I read such file by lines? Can I find a word and then read only some contents after the word?

nick262b, thanks for the reply. I don't know C++. I can not use other programming languages at work either since such software are not available (I heard that Matlab can do that too). So, I try to explore VBA.

RE: How to find a "word/string" in a text file if I don't use Cells of Excel

IDS, thank you!

RE: How to find a "word/string" in a text file if I don't use Cells of Excel

search online for "split string vba"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close