Text to columns with two names

Question

HiI know how to use text to columns, with a space, comma etc but how do i split names&nbsp;A1Mr Smith and Mr PotterMiss White and Miss Excel and Miss AxelMr OnlMiss Patterson and Mr Magnusson&nbsp;&nbsp;I want to see&nbsp;&nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; E1&nbsp; Mr Smith&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Mr Potter2&nbsp; Miss White&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Miss Excel&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Miss Axel3&nbsp; Mr Onl4&nbsp; Miss Patterson&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Mr Magnusson

riny_van_eekelen · Answer

SGeorgie&nbsp;Do you really need to keep the word "and" in its own column? If not, you can use TEXTSPLIT (still Inserders/Beta I believe) or Power Query. both allow the use of " and " as a delimiter. Something text-to-columns can't.See attached.&nbsp;And then there's always VBA to split the text, I guess. But that's not my thing.&nbsp;&nbsp;

sgeorgie · Answer

The 'and' is not needed. I just want to know number of people

Forum Discussion

Text to columns with two names

Share

Resources