Splitting text in cells based on criteria.

[Wamhoi]

Hi all,

I'm trying to break up text in excel based on certain criteria and would prefer using a formula.

Criteria one: Must be 35 characters or less in length
Criteria two: Must include full words

So for example:

Cell A1: Sally bought a new purse

End Result:
Cell A1: Sally bought a new purse
Cell B1: Sally bought a (assume 35 characters)
Cell C1: new purse (assume 35 characters)

As mentioned earlier the only caveot is that it cannot break individual words IE: "Sally bought a ne"

 

[BenMiller]

Will you have cells with more than 70 characters?

 

[Wamhoi]

Yes, in rare cases. So I'd have to utilize a third column.

 

[BenMiller]

So, for instance, if a cell contains sixty characters; the first cell should return 35 (assuming it breaks the words appropriately) and the second should return 25. Correct?

 

[Wamhoi]

Yup, thats correct.

 

[BenMiller]

This works for your first cell.
=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",100)),(100*(35-LEN(SUBSTITUTE(LEFT(A1,35)," ",""))))))

 

[mvptomlinson]

Try:

First cell (array formula, confirmed with CTRL+SHIFT+ENTER, not just ENTER):

=LEFT(A1,MATCH(2,1/(MID(A1,ROW(INDIRECT("1:36")),1)=" "))-1)

Second cell (array formula, confirmed with CTRL+SHIFT+ENTER, not just ENTER):

=IF(LEN(A1)-LEN(B1)<=35,MID(A1,LEN(B1)+2,LEN(A1)),MID(A1,LEN(B1)+2,MATCH(2,1/(MID(A1,ROW(INDIRECT(LEN(B1)+2&":"&71)),1)=" "))-1))

Third cell (non-array formula):

=IF(LEN(A1)>70,MID(A1,LEN(B1)+LEN(C1)+3,LEN(A1)),"")

 

[dcofer]

I was following along this thread as it is something that would help me as well. However our rows must contain no more than 61 characters.

I changed the 35 to 61 in the first formula (for Cell B1) and it worked perfectly. However in the second formula (for C1), it returns the next word only after the words in Cell B1, instead of the next 61 characters. Is the second formula correct? Or did my changing it to 61 characters mess it up?

Thanks for your help.

David

 

[mvptomlinson]

David, please don't jump into a thread requesting something different than the original poster, especially when that poster hasn't even gotten back to us whether it worked or not.

However...

In the first formula make two changes. I added an IF function to check if the original cell is less than 61 characters and if so, just return the original cell value. This should be done in my previous thread, which hopefully I can do in a minute.

=IF(LEN(A1)<=61,A1,LEFT(A1,MATCH(2,1/(MID(A1,ROW(INDIRECT("1:62")),1)=" "))-1))

In the second formula change "<=35" to "<=61" (near the beginning), and change "&71" to "&123" (near the end).

In the third formula change ">70" to ">122".

Can't edit my previous post with formulas, so to limit the first cell to 35 use this updated formula:

=IF(LEN(A1)<=35,A1,LEFT(A1,MATCH(2,1/(MID(A1,ROW(INDIRECT("1:36")),1)=" "))-1))

 

[dcofer]

Thank you for your help. Sorry for the breach of etiquette.

David