Hello,
I have a row of data. In each cell, I would like to add a line break everytime the text in that cell has more than 41 characters.
But I have no idea how to do that.
Does anyone know how this can be achieved?
Thank you,
Nadir
PS: I use Excel 2007.
1) Text in A1, in B1:
=IF(LEN(A1)<=41, A1, LEFT(A1,41) & CHAR(10) & MID(A1, 42, LEN(A1)-41))
2) Copy that down.
3) Copy the range of new values
4) Click on A1, then Edit > Paste Special > Values to replace the original values
5) Clear column B
Of course you can use any empty column for this.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hello,Originally Posted by JBeaucaire
Thanks for your help. I entered the formula you provided but Excel tells me the formula contains an error.
I have attached a sample file with the formula I used for you to see. I hope you can help me out.
Thank you
1) I removed the text at the beginning of the formula and it worked immediately, no edits.
2) I formatted column C to turn on Word Wrap, so the line feeds "effect" are now visible.
3) Now that I see you're splitting sentences, you probably don't want to split in the middle of a word, no? So switch to this formula:
=IF(LEN(A2)<=41, A2, LEFT(A2, FIND(" ", A2, 35)) & CHAR(10) & MID(A2, FIND(" ", A2, 35)+1, LEN(A2)))
I cannot thank you enough for your help. Everything works fine, and yes indeed, I forgot about not splitting in the middle of a word, thank you
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks