Truncate At Specific Character Formula - And More. - Help Needed.

[TheProffesional]

This is probably an easy one, but I'm a bit confused on how to do it. Plus this would probably help others in the future as well. I basically need to take any amount of characters and truncate it to 150, BUT if it does not have a period on the end then it needs to be truncated to the closest period.


Example:

I saw a man climbing down a water pipe. He had a knife in his hand. I hit his hand with a brick. He fell down on the ground and I jumped upon him. Soon others reached there and we handed him over to the police.

Needed Solution:

I saw a man climbing down a water pipe. He had a knife in his hand. I hit his hand with a brick. He fell down on the ground and I jumped upon him.

Current Solution that needs fixing:

Formula: =LEFT(C2,150)

I saw a man climbing down a water pipe. He had a knife in his hand. I hit his hand with a brick. He fell down on the ground and I jumped upon him. Soo


-------All help greatly appreciated.

[TheProffesional]

P.S if mod, can update the title of this thread to just truncate at specific character formula - I think that would be better and more neat because the title is the first thing, I wrote and wasn't really thinking about it.

[kvsrinivasamurthy]

If String is in A1, Try this

Please Login or Register  to view this content.

[Debraj Roy]

Try this..

Please Login or Register  to view this content.

to the closest period

in above case closet period always greater than 150..
Do you need it less that 150 alwasy..

[Glenn Kennedy]

This is cumbersome, but does return the desired result...

Please Login or Register  to view this content.

[TheProffesional]

If String is in A1, Try this

Please Login or Register  to view this content.

This one does not work for most cases.

[TheProffesional]

Try this..

Please Login or Register  to view this content.

in above case closet period always greater than 150..
Do you need it less that 150 alwasy..

This works for all cases, except it needs to be under 150 characters. Not over 150.

[TheProffesional]

This is cumbersome, but does return the desired result...

Please Login or Register  to view this content.

This one works for all, EXCEPT when it does not find a period before 150, then it just gives #VALUE!

[Debraj Roy]

then try this..

Please Login or Register  to view this content.

[tigeravatar]

Figured I'd throw my hat in the ring...

Please Login or Register  to view this content.

If there is no period in the first 150 characters, it simply returns the first 150 characters. Do you want it to return something different?

[TheProffesional]

then try this..

Please Login or Register  to view this content.

This one does make it under 150, but cuts it off in the middle of the sentence.

[TheProffesional]

Figured I'd throw my hat in the ring...

Please Login or Register  to view this content.

If there is no period in the first 150 characters, it simply returns the first 150 characters. Do you want it to return something different?

This one works pretty well under the conditions that i tested it on, but It needs to be tweeked to end on a period with a space because i noticed that if there is a word with a period in it, it cuts it off there. I tested the tweeked version and so far, it works pretty well. Going to test it on the full file that i have at home and update if it worked or not.

[Debraj Roy]

now got it..
you want complete sentence.. then try this..

Please Login or Register  to view this content.

[kvsrinivasamurthy]

TRy this Array formula.Press Ctrl+Shift+Enter together insted of Enter Key.

Please Login or Register  to view this content.

[TheProffesional]

This one works pretty well under the conditions that i tested it on, but It needs to be tweeked to end on a period with a space because i noticed that if there is a word with a period in it, it cuts it off there. I tested the tweeked version and so far, it works pretty well. Going to test it on the full file that i have at home and update if it worked or not.

This WORKS! No errors for all them. Thanks a lot.

[TheProffesional]

now got it..
you want complete sentence.. then try this..

Please Login or Register  to view this content.

This still gives #Value! for some of them.

[TheProffesional]

TRy this Array formula.Press Ctrl+Shift+Enter together insted of Enter Key.

Please Login or Register  to view this content.

Thanks for the formula, but I found one that works well already. Either way thanks for helping out.