Cell shatracter length limiting.

[Bob]

I am pasting data into a cell but want to limit it to a specific length. Is
there a function that will limit the text to the length required and carry
the remaining text onto the next row? Data validation does not work, it will
give me a warning but wont automatically cut the remaing text onto the next
row.

For example, copied text is 30 characters long. When i paste it into the
cell (which i want to limit to 20 characters long) it will automatically fill
one cell and paste remaining text into cell below.

Any help would be much appreciated

 

[MartinW]

Hi Bob.

Excel is not big on formatting. It may be better to paste into
Word first to get it nice and tidy and then then copy to Excel.
Not real pretty but at least you will achieve your goal.

HTH
Martin

 

[Roger Govier]

Hi Bob

You can't make a Copy and Paste put the data into 2 separate cells.

Having pasted your copy you could format the receiving cell as the width
you want and
Format>Cells>Alignment>Wrap Text
the cell height will adjust as the remainder of the text wraps below the
first line.

Otherwise you would have to do it within a macro with something like

Range("B1") = Left(Range("A1"), 20)
Range("B2") = Mid(Range("A1"), 21, 10)