Break a line of text at specfied character count

[kazphilips]

I have a file containing about 30k business names. In order to import this into another program the name must not exceed 35 characters. Can I break the name into two cells at the last space before the 35 character limit within the name is reached? For example:

Column A: Cardiovascular Diseases & Interventional Cardiology
Column B: Cardiovascular Diseases &
Column C: Interventional Cardiology

[NeedForExcel]

Try This in B1 - =LEFT(A1,FIND(" ",A1,25)-1) & in C1 - =RIGHT(A1,(LEN(A1)-1-LEN(B1)))

It will work in most cases though!

Deep

[kazphilips]

That works for the business names which exceed character limit, but the shorter names (ABC Surgical Centers) produce an error.

[NeedForExcel]

Here!

In B1 - =IF(LEN(A1)>35,LEFT(A1,FIND(" ",A1,25)-1),A1)

In C1 - =IFERROR(RIGHT(A1,(LEN(A1)-1-LEN(B1))),"")

Deep