How do I limit characters in a cell?

C

Casey

I have a cell with this information: "1234_01_251225". I need to have a
formula that will only give me the first 4 characters "1234". Is there a way
to do this? I need to add the formula to the entire column of data, since I
have thousands of rows of data.
 
P

Paul D. Simon

Data>Validation, Settings Tab.
In the Allow field, choose Text Length.
In the Data field, choose which option you want (but I would recommend
keeping it set to the default of "between").
With "between" as the Data option, enter a number in the Minimum field
(probably 0 in your case) and then the limit you want in the Maximum
field.
Copy that cell to as many other cells in the worksheet where you want
this limitation.

Now, users will not be able to enter more characters in the cell(s)
than the limit you set.

Hope this helps - let me know.
 
N

Nolene

I need to do basically the same thing -- allow folks to enter data over the
limit, but truncate it to the limit upon exiting cell -- but when I try this
formula in the cell where the data is entered, I get a circular reference
error.
 
G

Gord Dibben

Have your formulas in adjacent cells in a helper column.

Column A is where you enter the data.

Column B is where you have the formula.


Gord Dibben MS Excel MVP
 
Top