Compile Numerical data based on Keywords in a Sentence

Hello,

I am looking for VBA code that will allow me to:

1) Import a .log file into excel.
2) Look for key words in a line of text.

a) The lines of text I am interested in will all have the pattern: X purchases the Y for Z dollars of his allowance.

3) If the key words are in a line of text, extract the number within that same line of text.
4) Create a category for the subject of that line of text.
5) Add the number within the line of text to two separate cells:

a) To one cell that tracks the total spent for the subject of that line of text.

b) To one cell that keeps a running total for the entire document.

---

Example of a typical .log file is as follows:

The store opened on Tuesday afternoon.
Dexter purchases the Vase for 8 dollars of his allowance.
Ralph purchases the Vase for 7 dollars of his allowance.
The store closed on Sunday morning.
The store opened on Monday evening.
Carl purchases the Vase for 6 dollars of his allowance.
Jim purchases the Vase for 8 dollars of his allowance.
Ralph purchases the Vase for 10 dollars of his allowance.
The store closed on Wednesday afternoon.
The store opened on Thursday evening.
Ralph purchased the Vase for 11 dollars of his allowance.
Carl purchases the Vase for 7 dollars of his allowance.

I would need Categories that show the following data from the text above:
Dexter = 8
Ralph = 28
Carl = 13
Jim = 8
Total store profit = 57

Thanks in advance

This should do

Please Login or Register  to view this content.

Originally Posted by Leith Ross

Hello phoenixjasper,

Welcome to the Forum!

The attached workbook produces 3 columns: Name, Amount, Total. The Total column is actually the running total for each person. Have a look a tell me if this close to what you want.

There is a button on "Sheet1" named "Run". Clicking this button will prompt you for the file to open. It will list ".txt" and ".log" files in a folder. You can also choose "All Files" if you need to.

Here is the macro attached to the "Run" button. This is already installed in the attached workbook.
[/code]

Thank you so much, Leith Ross! Your code was awesome and got me pretty much what I was asking for. The 'Run' button was super helpful, too! Very easy to work everything^^

Originally Posted by jindon

Please Login or Register  to view this content.

Jindon, thanks a bunch! This code helped me understand more about how VBA works. It also gives me the chance to build on my math project for my kids to possibly include other 'key words'. This was awesome! Tysm!