Trying to extract names from a row while maintaning certain descriptors

[novawaly]

Hi, 

I have a data set that just lists company names in rows. The format is generally Type of Company [ Name 1; Name 2] Type of Company [Name 1], Type of Company [Name 2, Name 3, Name 4]. It's like this for about 40 rows

I'm trying to figure out the best way to transform this into a more usable format where each company name is in one column with the type of company next to it so like

Column A  Column B

Type           Name 1

Type           Name 2

However, if that's too difficult - just a list of names with the Type being a header in the row

Type

Name 1

Name 2

NAme 3

Type

Name 1

etc. I've been playing abour with RegEx but I cant seem to get it to to work - any help would be greatly appreciated. 

[binuacs · Accepted solution]

@novawaly  are you looking for something like this?

[novawaly]

exactly that - thank you so much!

[novawaly]

Where is the best resouce for learning about regEx. I'm a complete beginner there and when i read the docs here, it's just over my head. 

I attempted to do it by text to columns and using a formula to denote "Header" then a multirow formula but that only works b/c my data set is small and I can identify all the company types by using the browse tool. I'd love to be able to understand what/how you did that.