Data Splitting/Column Divider Automatization

Greetings!

I am looking for a solution regarding a rather specific objective I find myself dealing with. I am to find a way that would allow data to be split into different columns automatically. So far, the only thing I found was the Text to Columns Wizard, but that is manual, and I would need something that could do that automatically. And to be make that a little more complicated, there should be certain words that are recongized and added to a different column than the rest of the data.

Is making such an automatization possible? If yes, how would one go about that? And if not, could you recommend another method to separate data automatically by any means?

Your help will be much appriciated!

Welcome to the forum.

Yes, and will be even easier if you have 365 - is it really 2021 that you have?

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Great - look forward to seeing it.

Text colour/bold/italics, etc are not values that can be exploited by a formula. They have no effect on the underlying data, so this can not be done by formula. If there was some logic to this... the first (or last) name was the leader... it's easy. Otherwise, it's a job for VBA.

Shall I move the thread to the VBA sub-forum... or do you want to have another think about this.

How do you know WHICH of the names is the team leader? How else wre they distinguishable? Is there a list somewhere?

Set up a leader list somewhere in the workbook:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	N
1	Leader
2	Anna
3	Corey
4	Matt
5	Tina

Sheet: Munka1

Then in C2 copied down:

=IFERROR(FILTER($N$2:$N$5,ISNUMBER(SEARCH($N$2:$N$5,A2))),"")

and in B2 copied down:

=IFERROR(TEXTJOIN(", ",1,TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2,C2,";")," ",""),,";",1)),"")

Or do the lot in a single calculation (delete all expected results first). If you need more room for new names/leaders, increase the bits in red to whatever is reasonable & future-proof.

Formula:

Please Login or Register  to view this content.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Jusy extend the ranges to accommodate the extra data.

If you read the comments at Post 10, you will see that Ihave already told you how to do that. However I would advise against usingwhole columns (1,00,000 ++ rows). Use something sensible, but future-proof.

Try this:

=IFERROR(FILTER($N$2:$N$5,(ISNUMBER(SEARCH($N$2:$N$5,A2)))*($N$2:$N$5<>"")),"")

and this:

=IFERROR(TEXTJOIN(", ",1,TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2,C2,"")," ",""),,";",1)),"")

The "invisible man" tries once again. Mine works perfectly, without any adjustment.

WHO are you talking to???

Kindly make it CLEAR.

Yes, it does - look at the attachment I shared.

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C
2	Worker; Leader1	Worker	Leader1

Sheet: Munka1

Look at Glenn's suggestion instead - it's obviously better than mine.

My suggestion does work in the workbook you provided.

Try Glenn's instead.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.