separate into two columns
 

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.

separate into two columns
 

=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" wrote in message
...
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.

separate into two columns
 

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" wrote:

=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" wrote in message
...
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.

separate into two columns
 

=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)

"gwbdirect" wrote in message
...
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" wrote:

=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" wrote in message
...
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.