Advance Text to Columns needed

[Abbas999]

Hi Everyone,

Need Some help. I have sheet with Title + Company in single excel cell. There are about 590 Rows.

Examples

Owner at SPP Company
CEO and President at Zisoo Co INC
Principal of ILT PVT Limited.
Founder and Managing Director - IPKA Tel INC

As you can see I cannot use Excel Text to Columns feature on these rows to Split Title and Company Name into separate columns.

Some titles have at some of and - or commas. Is there a quick way of doing it?

 

[XLearner]

Hi,

Assuming the list starts in A1.

1. In B1:
   =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," at ",",")," of ",",")," - ",","),", ",",")
2. Copy down until row 590
3. Copy B1:B590 and paste the Values in B1
4. Text to column

Regards
XLearner

 

[Peter_SSs]

Or you could consider just using similar formulas to extract the two parts:

Excel Workbook
	A	B	C
1	Owner at SPP Company	Owner	SPP Company
2	CEO and President at Zisoo Co INC	CEO and President	Zisoo Co INC
3	Principal of ILT PVT Limited.	Principal	ILT PVT Limited.
4	Founder and Managing Director - IPKA Tel INC	Founder and Managing Director	IPKA Tel INC
5	Owner,SPP Company	Owner	SPP Company
6	Owner, SPP Company	Owner	SPP Company
Split Text

 

[Abbas999]

Thank you @XLearner and @Peter_SSs This really helped me 90% is good. The formulas are good. But I have to check few rows manually because some lines have bad format so the formula is not working on them. Anyway this has saved a lot of time. Thank you very much for helping.

 

[Peter_SSs]

You are welcome. Glad to help.