Extract Data from rows into columns

Hi,

I'm using Excel 2007 and I need to extract values from rows from a data dump into columns. Here is sample data (this is dummy data, but the format is the same):

> Log for Account 55555 Institution Number 55
>
> ----------- text text text text text text
> Certificate 5555555555 line number 555 via USD 555,555.00-
> text text text: date number percent 0 percent 0
> number text text text text text text
> text text text text text date; text text text date
> Text text text text percent text text
text text text number text. Text text text text text.
Text text text text text text percent
Text text text USD 5,555.00 text text text text text text
> Text text text text text text text
>
> ----------- text text text text text text
> Certificate 5555555562 line number 555 via USD 555,562.00-
> text text text: date number percent 0 percent 0
> number text text text text text text
> text text text text text date; text text text date
> Text text text text percent text text
text text text number text. Text text text text text.
Text text text text text text percent
Text text text USD 5,555.62 text text text text text text
> Text text text text text text text
>
> Log for Account 44444 Institution Number 44

....

I want to extract the number values (in bold) into a sheet in columns that will look like this:

Account Certificate Amount Int. Amount
55555 5555555555 555,555.00 5,555.62
55555 5555555562 555,562.00 5,555.62
44444 ....

The data dump continues and and each Account can have any number of Certificates in it. Each line of dummy data represents a new row. What's the best way to go about extracting this data?

Thanks

Hi Leith,

Thanks! No, Account, Certificate... etc will be the name of the columns, I just want the numbers in the columns. Thanks for your help!

Sorry Leith,

I misunderstood the question. Yes, the words will precede the values. Sorry for the confusion. Thanks!

Hi Leith,

Thank you so much!! This worked great. Except, it's only reading the interest amount and not the initial amount. The initial amount line has six spaces in between USD and the amount and looks like this:
> Certificate 555555555 text text number text USD 555,555.55-

(I don't think the post shows the six spaces, so it was missed when I first posted it)
So for the initial amount there is six spaces in between USD and the dollar amount. The interest amount line does not have spaces and looks like this:
Text text text USD 5,555.55 text text text text text text

Thanks a lot!

Hi Leith,

It looks like this:

> Certificate 555555555 text text number text USDspacespacespacespacespacespace555,555.55-

Thanks!

Hi Lee,

I think I have a similar problem. The answer is probably somewhere in the forum, but I looked around and I can't figure it out on my own. Basically, I copied some data off a website and now I am trying to organize it so it is usable. Here is my example.

I would like to put the data below into usable columns. The only data I need is the hospital name (column A), location (column B), Births (column C), and Beds (column D).

This is how the data currently appears in excel:

Banner Desert Medical Center*— Mesa, AZ
Hospital Type General medical and surgical ER Visits: 83,212
Admissions/Visits: 41,542*Inpatient*/ 133,208*Outpatient Births: 7,552
Surgeries: 8,793*Inpatient*/ 9,581*Outpatient Beds: 518
Banner Thunderbird Medical Center*— Glendale, AZ
Hospital Type General medical and surgical ER Visits: 65,827
Admissions/Visits: 29,189*Inpatient*/ 256,950*Outpatient Births: 4,821
Surgeries: 10,448*Inpatient*/ 4,660*Outpatient Beds: 376

Thanks for any advice!

Might help to see the actual file (attached)....