For a column of cells, separate the text in a single cell across multiple columns

New task for work today, which involves creating a spreadsheet for all existing members of an organization. I went to the organization's website and copied all of the names & info, then pasted into a blank spreadsheet. Thankfully, all of the entries were separated into their own rows, but all of the information is only in one column. Take a look at my sample:

(NOTE: does not contain actual names or info) Book1.xlsx

This sample only contains 5 entries, but my actual list contains about 200; if it had just been the 5, I would have been fine with manually separating the information, but for 200 entries I need something much faster. You'll notice in the sample that the company, person, address, phone number, email, and website (when there) are not separated by anything. I know that using Text-to-Columns, I would technically be able to achieve what I need using the Delimited option, but I can't imagine this working without separators. I thought perhaps there might be a way to separate them based off font changes or something? Or maybe some way that I can insert a semi-colon or some kind of separator between the necessary data?

While the font is Arial for the majority of the entries, in terms of font changes: company font size is 12 and color is navy blue; the person's name is size 18, bolded, and dark grey; the address & phone number are size 9 and the color is light gray; and finally, the email & website are also size 9, but navy blue in color. However, you'll notice that two of the five entries are formatted differently. This is because formerly, the entire cell & its text were a hyperlink to the email. I went ahead and removed these hyperlinks, simply using a "Remove Hyperlink" macro walkthrough I found on the web, but now these cells are uniformly set to Calibri and size 11, my default font setting.

I really have no idea what my options are in trying to achieve this separation, but I'm open to anything you guys can offer. You were all very helpful with my last post, and I'm hoping for the same results this time too. If this is something that requires VBA or macros, I'll need some step-by-step instructions to help me along; while I consider myself fairly proficient with excel in general, I'm still completely new to macros & VBA.

Thanks for any help you might have!

I did try that, but the webpage doesn't really cooperate with me there. However, I did manage to copy&paste the data with everything separated how I desired. It's all still in one column, so now it's a matter of rearranging everything into proper format.

Since the number of rows for each entry varies, I figured the best way to go would be to base the formatting around the cells containing phone numbers. The phone number is always the 4th row of each entry, so is there a way to say:

"If a cell in Sheet1 CONTAINS ###-###-####, then in Sheet2 place the 3rd cell above that one (Company Name) in the 1st available cell in Column A. Place the 2nd cell (Name) in the same row, but in Column B. Place the 1st cell (Address) in the same row, but in Column C. The cell that contains the phone number then gets placed in Column D......"

Essentially, that's what I'm now trying to achieve. My difficulty is that I'm not sure how to handle the cell(s) below the phone number, since each entry will either have just one cell for email, or two cells for email & website. Cleary the 1st cell below each phone number can be put in Column E, but for the websites (2nd cell below phone number) I think it would probably have to be something along the lines of "If the 2nd cell below a phone number (A6, for example) in Sheet1 does not match the cell in Column A on the row below in Sheet2 (If Sheet1!A6 doesn't match Sheet2!B1), then enter that cell in Column F. If it matches, do nothing and proceed with the rest of the data.

So the end result should be that for every 5 or 6 rows in the first sheet, there is a dedicated row with 5-6 columns on the second sheet. What would be even better is if the formula rearranges the cells to have the same effect, so I don't have an extra sheet. Thanks for the help!

New Workbook: Book2.xlsx

Thanks newdoverman, I'll give that a shot as soon as I get home!

Yes! That works Martin, thank you!