Splitting cells and rearranging data

[anandis]

I have a very messed up databook which now needs to be cleaned up and data segregated into columns.

I am at a loss to do this in a reasonably simple way because every "contact person" column has to be split into two or three columns and two or three rows of name, designation and cell phone number (i've made them all 123456789 for the example)

Would be grateful for help on a way to accomplish this?

Workbook extract attached.

Thanks.

[AliGW]

Administrative Note:

Is you forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

[JohnTopley]

Please add expected results to your file.

[anandis]

I have updated the excel file with the required format in the second sheet.

My excel version is 2016.

Thanks !

[AliGW]

Pleae update your forum profile - do this NOW as explained in my post above.

[sandy666]

what about Manager Director, Purchase, MD ?

but maybe

Sr. No	Company	Person	Title	Contact1	Contact2	Address1	Address2	Email1	Email2	Email3
1	A	Mr R		123456789
2	B	Mr S B		123456789		City,State,Zip		email id 1for person 1	email id 2 for person 2
2	B	Mr N P	Engineer	123456789		City,State,Zip		email id 1for person 1	email id 2 for person 2
3	C	Mr N T		123456789		City,State,Zip
3	C	Mr K T		123456789		City,State,Zip
4	D	Mr R		123456789
5	E	Mr Y		123456789		Address 1 :City,State,Zip	Address 2 :City,State,Zip	email id 1for person 1	email id 2 for person 2	email id 3 for person 3
5	E	Mr A J		123456789		Address 1 :City,State,Zip	Address 2 :City,State,Zip	email id 1for person 1	email id 2 for person 2	email id 3 for person 3
5	E	Mr D A		123456789	123456789	Address 1 :City,State,Zip	Address 2 :City,State,Zip	email id 1for person 1	email id 2 for person 2	email id 3 for person 3
6	F	Mr A		123456789
7	G	Mr S B		123456789		Address 1 :City,State,Zip	Address 2 :City,State,Zip	email id 1for person 1	email id 2 for person 2	email id 3 for person 3
7	G	Mr R D		123456789		Address 1 :City,State,Zip	Address 2 :City,State,Zip	email id 1for person 1	email id 2 for person 2	email id 3 for person 3
7	G	Mr H		123456789		Address 1 :City,State,Zip	Address 2 :City,State,Zip	email id 1for person 1	email id 2 for person 2	email id 3 for person 3
7	G	Mr S B		123456789		Address 1 :City,State,Zip	Address 2 :City,State,Zip	email id 1for person 1	email id 2 for person 2	email id 3 for person 3
8	H	Mr P S	Manager	123456789		Address 1 :City,State,Zip	Address 2 :City,State,Zip
8	H	Mr B B A	Director			Address 1 :City,State,Zip	Address 2 :City,State,Zip
9	I	Mr V A	Purchase	123456789		Address 1 :City,State,Zip		email id 1for person 1	email id 2 for person 2	email id 3 for person 3
9	I	Mr V C	MD			Address 1 :City,State,Zip		email id 1for person 1	email id 2 for person 2	email id 3 for person 3

[Glenn Kennedy]

Apart from Mr, what other titles are present? Do they ALL ALWAYS end with a full stop?

Edit: forget it. My approach will not work. Your best bet, by a LONG way, is Power Query or VBA.

[anandis]

I have done this.

[Glenn Kennedy]

You have done what??

[anandis]

How did you do this ???

what about Manager Director, Purchase, MD ?

[anandis]

Oops was replying to the moderator about updating my profile with version of excel and forgot to quote the message .

You have done what??

[sandy666]

How did you do this ???

answer my question first

[anandis]

Sorry i missed replying. Yes that column of designation needs to be there too.

what about Manager Director, Purchase, MD ?

[Glenn Kennedy]

Lol. OK. Thanks.

[sandy666]

so you can use Power Query with splitting, extracting, merging, replacing, moving, renaming, trimming, if-ing etc.

[anandis]

so you can use Power Query with splitting, extracting, merging, replacing, moving, renaming, trimming, if-ing etc.

Would very much appreciate more guidance. Could you please share the query worksheet you showed above? I dont quite know how to split this into rows .


Thank you

[sandy666]

next time try to answer with more details and do better example Before and After

here is:

---

If that takes care of your questions hit Add Reputation at the foot of the posts which have helped you

[anandis]

Thank you ! I was able to split the names into different columns but unable to do so with the email addresses. I'll keep trying.

next time try to answer with more details and do better example Before and After

here is:

---

If that takes care of your questions hit Add Reputation at the foot of the posts which have helped you

[sandy666]

you didn't show what you want to do with Mail ID column