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.

=LEFT(A1,4)

--
Regards,
Dave


"Casey" wrote:

> 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.

=LEFT(A1,4) assuming your data is in A1.
--
Brevity is the soul of wit.


"Casey" wrote:

> 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.

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.

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.

"Dave F" wrote:

> =LEFT(A1,4) assuming your data is in A1.
> --
> Brevity is the soul of wit.
>
>
> "Casey" wrote:
>
> > 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.

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


On Tue, 3 Oct 2006 16:01:02 -0700, Nolene <(E-Mail Removed)>
wrote:

>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.
>
>"Dave F" wrote:
>
>> =LEFT(A1,4) assuming your data is in A1.
>> --
>> Brevity is the soul of wit.
>>
>>
>> "Casey" wrote:
>>
>> > 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.