limiting or deleting extra characters in a cell

  • Thread starter Thread starter dprichard
  • Start date Start date
D

dprichard

I have an excel file I need to import into a program. The program I a
importing the item into has limitations on the number of character
that can be in the cells. Some of the items in the excel spreadshee
have more then that number of characters. Is there any way I can limi
the number of characters that can be entered into a cell and have i
delete the extra characters that are in there?

Thanks in advance for any help
 
Hi there,

You could use Data Validation to limit number of characters in the Excel
spreadsheet. Data | Validation | Custom | ..

=LEN(A1)<2

Where A1 is the cell selected.

HTH
 
To control the number of character entered in the cell, you can use Data
Validation.

Select the cells where you want to limit the characters
Choose Data > Validation
From the Allow dropdown, choose Text Length
From the Data dropdown, choose Less than or equal to
In the Maximum box, type your maximum number of characters
On the Input message tab, you can add a message to notify users of the
maximum.
Click OK

To delete extra characters, you could use programming, or a formula in
another column. For example, =LEFT(A2,5)
Then, copy that column, and paste as values onto the column of characters.
 
If you have a large block of text in a cell, say A1. and you want to limit it
to 100 characters. In an un-used cell enter:
=LEFT(A1,100)

This will discard everything after the first 100 characters. this will fix
old entries


Use Data > Validation to limit the length of text strings for new user
entries.
 
Back
Top