Advance Text to Columns needed

Abbas999

Board Regular
Joined
Jan 4, 2015
Messages
102
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?
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

XLearner

Active Member
Joined
Apr 20, 2016
Messages
291
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,712
Office Version
365
Platform
Windows
Or you could consider just using similar formulas to extract the two parts:

Excel Workbook
ABC
1Owner at SPP Company
Owner
SPP Company
2CEO and President at Zisoo Co INCCEO and PresidentZisoo Co INC
3Principal of ILT PVT Limited.PrincipalILT PVT Limited.
4Founder and Managing Director - IPKA Tel INCFounder and Managing DirectorIPKA Tel INC
5Owner,SPP CompanyOwnerSPP Company
6Owner, SPP CompanyOwnerSPP Company
Split Text
 

Abbas999

Board Regular
Joined
Jan 4, 2015
Messages
102
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,712
Office Version
365
Platform
Windows
You are welcome. Glad to help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,099,574
Messages
5,469,475
Members
406,655
Latest member
pwilson06

This Week's Hot Topics

Top