Copy data from txt file to excel file then delete text file

Hi,

I am looking for a vba code that will open a specific text file and look for specific data. Then I want it to place that data in specified column in excel worksheet based on criteria of another column in same worksheet.

See below example data from text file:

Data Report for Tom Gray
January 31, 2018
Report By: Portfolio
Adjusted Market Underlying
Total Market Value: 23,734 2,140
04/19/18 05:18:10 AM Page 1 of 1

Using above text example, I need code that will look for the line that begins with "Total Market Value", then return the number after the SECOND space. In this case, it would be 2,140.
Then I want the code to place the number it found in column C of the excel sheet IF column A contains "Tom Gray".
So if Tom Gray was found in A11, then the 2,140 would be placed in C11.
Then I want the code to close and delete the text file.

Hi !

Attach both workbook and text file …

Detail « file extension name » as your attachment seems to not follow that …

I edited my previous post and have re-attached the txt file with the proper name.

Ok it's not about the file extension (aka .txt) but just on the file name without its extension (aka Tom Gray) !
But that's not really matter as this name is already in the first line of the text file …

According to your attachment the place where must reside the result seems to be obvious
but when reading again your original post I may be confused :

Originally Posted by gratedane8

I want the code to place the number it found in column C of the excel sheet IF column A contains "Tom Gray".
So if Tom Gray was found in A11, then the 2,140 would be placed in C11.

So whatever the text file if the name is found within column A so the result must always stand in column C of the same row
or maybe you had not very well explain your need ?

Marc L,

Apologies for not explaining this well.
The name is in the first line of the text file and in the file extension. I wasn't sure of the limitations of Excel, so I gave both as possible options to use for the code. If the extension does not matter and the name in the first line of the text file is sufficient for the code, then that's good.

So whatever name you find in the first line of the text file, if you find that same name in Column A of the excel file, paste the corresponding number found in the text file for that name to Column C of the same row.