Feb 12 2019 09:56 AM
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.
Feb 12 2019 10:32 AM
Here is a sample of what I am needing help with.
Feb 12 2019 11:50 AM
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?
Feb 12 2019 12:07 PM
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!
Feb 12 2019 07:29 PM
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.