Excel formula help

New Contributor

I received a download of 10K company names, contact person, addresses and phone numbers.  They were given to me all in column A.  Is there a way to move the addresses and phone numbers below the company name to different columns (address 1, address 2, city, state, zip, phone number) so that I can filter. Some addresses are one line and some are two lines.  Sometimes there is a name, sometimes there is no name.  I feel there is a quicker way to do this other than copy and paste.  Thanks. 

4 Replies

Here is a sample of what I am needing help with.

Do you have the word "Company" with each company name, or how do you know where is company name, where is person name or something else?

The word "Company" is not in each company name. But the company name is bolded.  There is a company name for each entry. And then additional details in the next 3 - 5 lines.  Does that answer your question?  Thank you for responding!  I am hoping to save someone a lot of copying and pasting!

That could be this way:

- select you entire column and copy it to the next one

- unbold first column (select and Ctrl+B)

- Ctrl+F->Options, Find what is empty, Format is bold font; Find All; Ctrl+A on bottom pane with list of cells found

- now you have all company names selected in second column. In formula bar type 0 and Ctrl+Enter. You shall have zero in that column instead of company names

- select your entire range in two columns, Data->From Table/Range, uncheck what your table has headers and start editing in Power Query editor

- add column Company (if second column 0 then company else null)

- add Index column from 0 and another one from 1

- add another column (if second column 0 then first index else null) and fill it down

- one more column (second index minus previous column minus 1)

- remove other columns but last one, company and first column

-  fill down company

- select last column, Transform->Pivot column, here select Company and in Advanced - All rows

- Land you query into the Excel sheet.


That's all. Please see second sheet in attached.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies