Trying to extract names from a row while maintaning certain descriptors

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.

CompanyScreeningReport (2).xls

Regex

Text Mining

Accepted answers

binu_acs

@novawaly are you looking for something like this?

Company.yxmd

All comments

binu_acs

@novawaly are you looking for something like this?

Company.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703