separate into two columns

[gwbdirect]

I need to take information in a spreadsheet (mailing list) and separate the
title columns into two columns because the title is too long.
Deputy Manager - Investment Fund - Vice President - Clients Relations &
Media & Communications
Want it to be:
Title 1 = Deputy Manager - Investment Fund - Vice President
Title 2 = Clients Relations & Media & Communications
Is there a formula that will move words into another column if the line
exceeds say 30 or 40 characters. Also can it do it without truncating a word.

 

[Bob Phillips]

=IF(LEN(A1)>35,TRIM(LEFT(A1,FIND("-",A1,35)-1)),A1)

and

=IF(LEN(A1)>35,TRIM(MID(A1,FIND("-",A1,35)+1,99)),"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[gwbdirect]

Hi Bob,
Thanks for the formula,
I tried it and if a cell is blank I get a 0. How can I add to the formula so
this doesn't happen and also some of the cells I get a value error
example: SVP & Chief Officer, Wealth Management #VALUE!

 

[Bob Phillips]

=IF(A1="","",IF(AND(LEN(A1)>35,ISNUMBER(FIND("-",A1))),TRIM(LEFT(A1,FIND("-",A1,35)-1)),A1))

and

=IF(AND(LEN(A1)>35,ISNUMBER(FIND("-",A1))),TRIM(MID(A1,FIND("-",A1,35)+1,99)),"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)