Limiting number of characters in a line

[Amejin]

I have a problem that I don't think is unique, but I haven't been able to find a resolution via the usual tools (Google, forums, etc).

I work for a localizer of text heavy software. Our developers normally use a converter to pull text out of a file and insert it into the program. This text is normally input into an Excel file since it has to correspond with files/locations in the program.

Since the space on the screen of the program is limited, we often face character limits when inputting text into Excel. Now, one cell might hold up to 255 characters, and I could restrict that with data validation, but within one cell I need to restrict each line (with a hard return) to 30 characters to fit on the screen.
So for example I have a row of cells that look like:
Hello, my name is Amejin. I like pizza, beer, and bowling.

Which need to look like:
Hello, my name is Amejin. I[HR]
like pizza, beer, and bowling.

Does anyone know a simple way to do this, or do I need to look for a macro and a VB expert?

[shg]

You'd need VBA, but it isn't difficult.

A macro to do this on the entire worksheet?

[Amejin]

You'd need VBA, but it isn't difficult.

A macro to do this on the entire worksheet?

Thanks for the quick reply!!

I don't need this to format the entire sheet, because I need to be able to specify the columns to be formatted. I suppose there must be a way to specify them as a range?

If it helps, our formatting usually ends up something like this:
｜A column ｜B column ｜C　column ｜...
｜ID　#1　 　｜English text｜French Text｜...
｜ID　#2　 　｜English text｜French Text｜...

In this case, the French text will be output and needs to be formatted to a certain line length. The way we do this now (the BAD way) is by adding line breaks by hand. With up to several thousand cells in a file it can take an entire day to do one file, which is clearly not very productive.

[excelmarksway]

This utility can import a text file into a textbox. You can edit your text then "export" it into a cell. The carriage return are accepted and although yo may not see the results directly, when you enter the cell, your formatting is shown. Great for large text files. Other options are provided for editing the text.
http://www.geocities.com/excelmarksway/

EasyText_Rev1.xls

[shg]

This is pretty brutal -- it whacks the string every 30 characters. Select the cells of interest and run it.

Please Login or Register  to view this content.

It could be modified to break lines at word boundaries that occur prior to 30 characters ...

[Amejin]

This is pretty brutal -- it whacks the string every 30 characters. Select the cells of interest and run it.
{CODE}
It could be modified to break lines at word boundaries that occur prior to 30 characters ...

You're a stud!

How would one modify that code to break lines at word boundaries? I'm already pretty lost in the code, but I'm guessing you'd have to work backwards from char #30 to the first space encountered and tell it to break there? I think I understand a bit of how it's supposed to work, but I just don't know the code to do it.

[Amejin]

Could a mod please move this to the Programming section since this is going to require vba?
Thanks. :D

[shg]

Try this:

Please Login or Register  to view this content.

[Amejin]

Gee that works perfectly. Thanks for taking the time to address this. I think I can work out how you did that in the code too so it really helps me get a better handle on how it's processing.
This site needs a reputation system so I can give you points for it.

[shg]

Would you please mark the thread as Solved?

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[subhajit_de]

When I am running it selecting the data in A1 the data remains unchanged .I have inserted the module and pasted the code and running from MAcros in the devloper"s tab pls advise

[davesexcel]

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.