Split sentence in Excel 2

[Histor]

Hi,
Could anyone tell if there is a way to to split a sentence to a equally two sentences?

in the above image, I have a full sentence in Column A, Each sentences have different numbers of words.
I want to split this column A to two equal sentences in column B and C without disturbing any words.

I have tried Text split. It splits all the words to multiple columns and I have to concatenate these words. But some rows has 5 words some rows has 12 words.
If the column A full sentence has same number of words, text split will help. This requires some manual work like filter same same numbers of words Rows to concatenate to make equal length sentence 1 and sentence 2.

But in my case column A has different numbers of words.

I want to split the column a sentence (line 1) to two equally long sentences to put line 1 and line 2. Any formulas?

 

[GregLocock]

You need to fill in the two columns Sentence 1 and Sentence 2 so we can understand what you are trying to do. How are you measuring sentence length?

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[Histor]

Hi @Greg,
Need to split the source sentence to equal lengths or close to equal lengths in sentence 1 and 2.
For example, if the source sentence has 6 words, I want 3 in each sentence 1 and 2. If source sentence has 7 words, I want either 4 and 3 or 3 and 4 in sentence 1 and 2.

 

[GregLocock]

can we assume there is always one space between each word and no trailing spaces? Is there a maximum number of words in a sentence?


Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[Histor]

Yes. There's always one space

 

[GregLocock]

It would be a trivial task in vba, it might be possible tidily using arrays, but here's a brute force method that breaks for odd numbers of words, with sentences of limited length. The ones in red are wrong








Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[davidbeach]

If splitting based leaving the strings as close to the same length, just find the first space past the midpoint of the first string and split there.

I’ll see your silver lining and raise you two black clouds. - Protection Operations

 

[IDS]

This basically as suggested by davidbeach.

With text in A2:
C2, find length or text: =LEN(A2)
D2, find length to midpoint: =INT(C2/2)
E2, find fist space at or after midpoint: =FIND(" ",A2,D2-1)
F2, extract first half: =LEFT(A2,E2)
G2, extract second half: =RIGHT(A2,C2-E2)

You could of course combine some of those columns if you want.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[davidbeach]

Yeah, two messy equations is what I had in mind, one in each result cell, but yep, that's the concept.

I’ll see your silver lining and raise you two black clouds. - Protection Operations

 

[IDS]

Yeah, two messy equations is what I had in mind, one in each result cell, but yep, that's the concept.

To do in a single cell, it would probably be much neater to use the new LET and/or LAMBDA functions, but I'm not really up to speed with those yet.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Histor]

Thank you all.

Using your tips, I have come up with the below formula and its working really well. Thanks again.

=LEFT(A2,FIND(" ",A2,INT(LEN(A2)/2-1)))
=RIGHT(A2,LEN(A2)-FIND(" ",A2,INT(LEN(A2)/2-1)))