Excel how can I truncate a cell after 50 characters?

[stengol]

I am impoting data via XML into an excel spreadsheet. One column of cells has a description in it. That description can be up to several hundred characters long and also may include carriage returns that are translated into a character that looks like a little square box. (which I cannot copy and paste by the way otherwise I would have put it here)What I want to do is to not only alter the cell width to 50 characters (easy enough) but to actually get rid of the excess ones so that when I send it to my customer they can't enlarge the cell and read them.

Any suggestions?

An automated process would be useful, if not then something I can apply to the whole column manually in one go.

All suggestions gratefully received

 

[KenWright]

Select Column and do Data / Text to Columns, Fixed Width, set marker at 50 characters and then select 'Do Not Import' on second column.

Can also use a helper column with a formula such as (assuming your data is in Col A, starting A1)

=LEFT(A1,50) and then copy down. Copy and paste special as values, then paste over original and then delete helper column.

As for prevention, you can use Data Validation to prevenet entry of more than 50 characters, but it would let them type hundereds of characters initially, and then tell them tough when they try to hit Enter. It's also easily bypassed using Copy and paste from a cell that has no D/V applied to it.

Could use code to try and do most of this, but I can't think of any way to physically stop them typing when they hit 50 characters.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[byundt]

If you want to get rid of the little squares, try turning on the Wrap Text feature. Do this by:
1) Selecting all the cells with the problem
2) Opening the Format...Cells menu item
3) On the Alignment tab, check the box for "Wrap text"

Brad