Text to Columns/Reg Ex help to clean a list of company names

[novawaly]

Hi, 

I've been extracting names using a screen grab tool and getting them into Excel and now attempting to clean them up. 

For some reason, some names extract fine and have one company per row, other times, it'll have 5-6 companies that'll be listed in one row. 

Another strange thing is looks it'll end a lot of rows with a 0 or a CJ or Cî (not sure why) but I'm attempting to use these characters to clean my list so that each company has it's own row. 

When I've tried to use text to columns and I split on 0, CJ - it seems to behave strangely and I cant quite figure out what/how to accomplish this. 

Is this something I'd need to use reg ex for?

[novawaly]

@binu_acs  Ok I see. 

Sorry - I feel so bad to keep going back and forth after you've already tried to help so much but The problem I'm having is that it's still not splitting rows that have multple company names into separate rows. My original data had 513 records which is the same as the final output. 

If you look at record number 512 - Zendar Inc. CJ Zenuity CJ are 2 different companies. I dont know why CJ is splitting it but ideally i'd have each name on a separate row

Zendar Inc

Zenuity. 

Some of cells will have 6-7 different company names. They seem to always be split by CJ, CI or 0

[binu_acs]

@novawaly np, how many fields are having the above mentioned issue (CJ in between?)

[novawaly]

I ended up just doing a find and replace in excel any CI, CJ for 0 then just using the text to columns and split on 0. Did the job for now. Thanks so much for your time and attention on this though! It was super helpful.