Search for text in txt file and copy the words to excel sheet

[aejaz7]

Hi,

I have a particular software which contain my client user name and it's password along with other misc details like address full name etc.
Client password going to expired after every three month. I have sheet which column A contain user name and column b for password.

Client password in text file is written after user name like

sampleusername     password1     
      sampleusername1 password2                              sampleusername3                                       password3

I would like to use excel vba to search the text file for particular user name and copy password in excel sheet.

Is there any code I could use to solve the problem? Thank you for your help.

 

[Excel_User_10k]

Hi, I am not an expert but thought I may be able to help. Is there a reason why you can't just copy all of the data from the txt file into Excel and have Excel pick out what you need with Forumula's such as Search or Text?

 

[aejaz7]

Hi, I am not an expert but thought I may be able to help. Is there a reason why you can't just copy all of the data from the txt file into Excel and have Excel pick out what you need with Forumula's such as Search or Text?

I can't because text file is around 8 to 10 mb big, have much lines and other data also contain like address, mobile no, email, file no, pincode etc.
one thing is common that password is after username and some space (space between user name and password also not fix).

 

[Excel_User_10k]

I can't because text file is around 8 to 10 mb big, have much lines and other data also contain like address, mobile no, email, file no, pincode etc.
one thing is common that password is after username and some space (space between user name and password also not fix).

So you can't do Ctrl+A, then copy and paste it all in an Excel Sheet, then use this method/Formula to Split it:

359K views · 18K likes | Senna Brennan on Instagram: "Use this tool to split your text (at any character) into columns🌟 Share & save this post for future reference! 💻💚 #excel #exceltips #exceltricks #spreadsheets #workhacks #finance #learnexcel #g

19K likes, 72 comments - easilyexcel on October 7, 2022: "Use this tool to split your text (at any character) into columns🌟 Share & save this post for future reference! 💻💚 #excel #exc..."

www.instagram.com

Then create another Sheet in the Same workbook to retrieve the data from the Columns that contain the usernames and passwords. (Hide the Sheet with the data if you want too, so the other data - such as addresses - can't be seen)

Hopefully that will work for you.

 

[aejaz7]

So you can't do Ctrl+A, then copy and paste it all in an Excel Sheet, then use this method/Formula to Split it:

359K views · 18K likes | Senna Brennan on Instagram: "Use this tool to split your text (at any character) into columns🌟 Share & save this post for future reference! 💻💚 #excel #exceltips #exceltricks #spreadsheets #workhacks #finance #learnexcel #g

19K likes, 72 comments - easilyexcel on October 7, 2022: "Use this tool to split your text (at any character) into columns🌟 Share & save this post for future reference! 💻💚 #excel #exc..."

www.instagram.com

Then create another Sheet in the Same workbook to retrieve the data from the Columns that contain the usernames and passwords. (Hide the Sheet with the data if you want too, so the other data - such as addresses - can't be seen)

Hopefully that will work for you.

text file data not in such format to use this formula.

i need some correction in below mentioned code.

Sub MrExcelTxt()
Dim FSO As Object
Set FSO = CreateObject("scripting.filesystemobject")
Dim Src As Object
Dim Str As Object
Dim Content As String
Dim Arr As Variant
Dim Arr2 As Variant
Dim x As Long, y As Long, p As Long
p = 1
Set Src = FSO.GetFile(InputBox("Enter Full file path here"))
Set Str = Src.OpenAsTextStream
Content = Str.ReadAll
Arr = Strings.Split(Content, ";")
For x = 0 To UBound(Arr)
    If InStr(Arr(x), "Year") Then
        Arr2 = Strings.Split(Arr(x), ",")
            Cells(p, 1) = Arr2(UBound(Arr2) - 1)
            p = p + 1
    End If
Next
MsgBox "Done"
End Sub