Format Data from Web so it's usable

Hello All, please help!

I think I have a similar problem as this thread http://www.excelforum.com/excel-prog...o-columns.html. 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!

Document is attached.

When you post a sample sheet, there should be a sample results page too so we don't have to guess.

Without using a macro (which is also an option), you could try the following formulas in another sheet (starting in A1, B1, C1 and D1):

A1: =LEFT(OFFSET(Sheet1!$A$1,ROW()*4-4,0),FIND(CHAR(151),OFFSET(Sheet1!$A$1,ROW()*4-4,0))-2)

B1: =MID(OFFSET(Sheet1!$A$1,ROW()*4-4,0),LEN(A1)+4,255)

C1: =OFFSET(Sheet1!$A$1,ROW()*4-2,3)

D1: =OFFSET(Sheet1!$A$1,ROW()*4-1,3)

Let me be the first to say that your "revised" spreadsheet shows results not in sync with your original request. Obviously, the formulas I provided could be adjusted to gather the other pertinent data. Perhaps give it a try and let us know if you get stumped..