Break Cell based on character count-w/o splitting words-Help

[mnfez]

Hey Folks,
I just registered for this forum but I have been using it for a while now looking for/finding help. Hoping someone out there can help me with an issue I run into frequently at work. We do large loads of new part #'s/descriptions/UPC's/etc from excel into our database. The fields are limited on character count and I would like help writing a formula to break the contents of a cell based on character count without breaking a word.
Examples
Column A Current Data
Row1: 12Pt HD Offset Striking Wrench 1-3/8" & 35MM (44 characters)
Row2: 12Pt 1/2" Drv Socket 27MM (25 characters)
Row3: 12Pt Satin Combo Wrench 11/16" (30 characters)
Row4: 12Pt 1/2" Drv Deep Socket 1-1/8" (32 characters)

Results I'm Looking For

Row1: Column B-12Pt HD Offset Striking Wrench Column C- 1-3/8" & 35MM
Row2: Column B-12Pt 1/2" Drv Socket 27MM Column C-No Data Returned
Row3: Column B-12Pt Satin Combo Wrench 11/16" Column C-No Data Returned
Row4: Column B-12Pt 1/2" Drv Deep Socket Column C-1-1/8"

Thanks in advance for any help you folks can offer.
-Eric

[daffodil11]

You just need to find the last space prior to exceeding the character limit.

Can you provide the character limit per line?

[AlKey]

Based on your example only:

In B1

=IF(ISERROR(LEFT(A1,FIND("-",A1)-1)),A1,LEFT(A1,FIND("-",A1)-1))

In C1

=SUBSTITUTE(A1,IF(ISERROR(LEFT(A1,FIND("-",A1)-1)),A1,LEFT(A1,FIND("-",A1))),"")

	A	B	C
1	12Pt HD Offset Striking Wrench 1-3/8" & 35MM	12Pt HD Offset Striking Wrench 1	3/8" & 35MM
2	12Pt 1/2" Drv Socket 27MM	12Pt 1/2" Drv Socket 27MM
3	12Pt Satin Combo Wrench 11/16"	12Pt Satin Combo Wrench 11/16"
4	12Pt 1/2" Drv Deep Socket 1-1/8"	12Pt 1/2" Drv Deep Socket 1	1/8"

[mnfez]

Need the cell to break at a 30 character max, and not break a word or sting of #'s.
Thanks

[daffodil11]

B1: =IF(OR(MID(A1,31,1)=" ",MID(A1,31,1)=""),LEFT(A1,30),LEFT(LEFT(A1,30),MAX((MID(LEFT(A1,30),ROW($1:$70),1)=" ")*ROW($1:$70))))

B1 is an array formula. It is confirmed holding Control+Shift+Enter to exit the cell.

C1: =TRIM(SUBSTITUTE(A1,B1,""))

Then copy these two downwards as needed.

text to columns with character limits.xlsx

[ChemistB]

Non-Array formula
In B1

Please Login or Register  to view this content.

In C1

Please Login or Register  to view this content.

[mnfez]

daffodil-that worked perfect, thanks!
chemist-that formula split cells that were under 30 character and moved that last word into the next cell, but close

Thanks for all the help!

[Tony Valko]

Try this for the formula in cell B1:

=LEFT(A1,LOOKUP(30,FIND(" ",A1&" ",ROW(INDIRECT("1:30"))))-1)

[ChemistB]

Minor modification to correct for that
=IFERROR(TRIM(LEFT(A1,FIND(" ",A1,LOOKUP(31,FIND("*",SUBSTITUTE(A1&" "," ","*",ROW(A1:A9))))))),A1)

and a simpler equation for C1
=TRIM(SUBSTITUTE(A1,B1,""))