How to format the value in a cell?

Hello!
I wonder if excel has the function to format a value in cell
without the help of Macro.
To format a value,here I mean to correct the value automatically.
For example,it need the value in a cell to be "T".But a user may
enter " T","T ","t".The formatting function used here to trim the
space at the begining or end of "T",or change the "t" to upper case.
--------------------
An

Try =TRIM(UPPER(A1))

--
Regards
Roger Govier
"An" <[email protected]> wrote in message
news:u%23Kg%[email protected]...
> Hello!
> I wonder if excel has the function to format a value in cell
> without the help of Macro.
> To format a value,here I mean to correct the value automatically.
> For example,it need the value in a cell to be "T".But a user may
> enter " T","T ","t".The formatting function used here to trim the
> space at the begining or end of "T",or change the "t" to upper case.
> --------------------
> An
>
>
>
>
>

You can't do this with formatting alone.

You'll need a macro.

If you want a macro, right click on the worksheet tab that should have this
behavior. Select view code and paste this into the code window:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

On Error GoTo errHandler:
If IsEmpty(Target) Then
'do nothing
Else
Application.EnableEvents = False
Target.Value = "T"
End If

errHandler:
Application.EnableEvents = True
End Sub

================

Actually, you can change the display to just show T--but it won't change the
value.

You could give the cell a custom format of:
T;T;T;T

But if you want to check that cell, you can't use:

=if(a1="T","ok","not ok")

But you could check to see if the cell was empty:
=if(a1<>"","ok","not ok")



An wrote:
>
> Hello!
> I wonder if excel has the function to format a value in cell
> without the help of Macro.
> To format a value,here I mean to correct the value automatically.
> For example,it need the value in a cell to be "T".But a user may
> enter " T","T ","t".The formatting function used here to trim the
> space at the begining or end of "T",or change the "t" to upper case.
> --------------------
> An

--

Dave Peterson

Thank you! Mr. Peterson.
To use Macro is hard for an average person.I think this function is an
useful one.
Why Microsoft didn't integrate it into Excel to cell's formatting,let user
use some
functions ,say,Upper(),Lower(),Trim() to format the value?
--------------------------------------------------------------------
An

Formats change the look of a cell--not the value.

all those =upper(), =lower(), =trim() actually can change the value.

You could use those functions in an adjacent helper cell, though.

Use one column for input and the adjacent column for what you think the input
should have been.



An wrote:
>
> Thank you! Mr. Peterson.
> To use Macro is hard for an average person.I think this function is an
> useful one.
> Why Microsoft didn't integrate it into Excel to cell's formatting,let user
> use some
> functions ,say,Upper(),Lower(),Trim() to format the value?
> --------------------------------------------------------------------
> An

--

Dave Peterson

Yes,use an adjacent cell,but not a good alternative,I think.

Hi An,

Except for some odd cases ( http://j-walk.com/ss/excel/odd/odd06.htm ), the
formulae in Excel can not insert, delete objects or change their properties.
They can only return values.
So you can't change the original cell value but you can of course get
theammended value in a different cell. Just explore the Help for the
following functions:

TRIM() - removes extra spaces
CLEAN() - removes non-printable characters
UPPER() - coverts to upper case
LOWER() - coverts to lower case
PROPER() - capitalizes the first letter
TEXT() - returns text in many different formats

and many other text functions

Regards,
KL



"An" <[email protected]> wrote in message
news:u%23Kg%[email protected]...
> Hello!
> I wonder if excel has the function to format a value in cell
> without the help of Macro.
> To format a value,here I mean to correct the value automatically.
> For example,it need the value in a cell to be "T".But a user may
> enter " T","T ","t".The formatting function used here to trim the
> space at the begining or end of "T",or change the "t" to upper case.
> --------------------
> An
>
>
>
>
>

Thank you for telling me the basic rule.but I don't think it
break the rule if this occurs during a user enters the value.

Well, it actually does if you want the value changed. As Dave has already
explained in his recent message to you, a format is not changing the value,
but the look and thus doesn't break the basic rule.

Regards,
KL


"An" <[email protected]> wrote in message
news:[email protected]...
> Thank you for telling me the basic rule.but I don't think it
> break the rule if this occurs during a user enters the value.
>
>

I learned that there are two states while the value is being entered,before
and after the entered value is accepted by a
cell.
I think the basic rule is for the accepted value.So changing the
value before it was accepted doesn't break the rule.

Regards,
An

An,

What you are saying is true for anything, but functions. For a function a
non-accepted value is non-existant, it will see only the accepted value. BTW
as far as I know, VBA has no built-in way of reading non-confirmed value
from a cell either. In a sense, there is something in Excel that could be
seen as a prototype of what you are after and it is the AutoCorrect (menu
Tools>AutoCorrect). See if you can get it to do whatr you want.

Regards,
KL


"An" <[email protected]> wrote in message
news:[email protected]...
>I learned that there are two states while the value is being entered,before
> and after the entered value is accepted by a
> cell.
> I think the basic rule is for the accepted value.So changing the
> value before it was accepted doesn't break the rule.
>
> Regards,
> An
>
>