How to format the value in a cell?

[An]

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

[Roger Govier]

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

[Dave Peterson]

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

[KL]

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

[An]

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]

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

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

[An]

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

[KL]

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

[An]

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

[KL]

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

[An]

AutoCorrect is for all the cells,not for a certain
part of the cells. And,in some cases,AutoCorrect is
not convenient.
Thanks!I asked the question because I used to learn
a software logically,Excel should have the function.

Regards,
An

[SuperDave]

I agree, the inability to "force" a case setting in Excel data entry is a problem. In Canada, postal codes are in the format A1B 2C3; while uppercase letters are not strictly required, it looks odd if they are not used. Things would be so much simpler if I could use a text format on a cell to "force" the characters to be uppercase, instead of having to use a macro (which the user may choose to disable) or a function (which is a "kludge" solution). Microsoft, are you listening?

One "workaround" that I have used is to change the cell font to something that displays all caps regardless of the key entered. Bank Gothic, Bremen, Charlesworth and several others on my computer have this capacity. With this option, even if you type "a1b 2c3" it will display as A1B 2C3. Whether or not this works for you will depend on what you plan to do with the text afterward - simple printing will be fine, but any data manipulation may force you to use the UPPER function to get the results you need.

[Dave Peterson]

There's a very good chance that MS is not listening.

You may want to send your request to:
[email protected]



SuperDave wrote:
>
> I agree, the inability to "force" a case setting in Excel data entry is
> a problem. In Canada, postal codes are in the format A1B 2C3;
> while uppercase letters are not strictly required, it looks odd if they
> are not used. Things would be so much simpler if I could use a text
> format on a cell to "force" the characters to be uppercase, instead of
> having to use a macro (which the user may choose to disable) or a
> function (which is a "kludge" solution). Microsoft, are you
> listening?
>
> One "workaround" that I have used is to change the cell font to
> something that displays all caps regardless of the key entered. Bank
> Gothic, Bremen, Charlesworth and several others on my computer have
> this capacity. With this option, even if you type "a1b 2c3" it will
> display as A1B 2C3. Whether or not this works for you will depend on
> what you plan to do with the text afterward - simple printing will be
> fine, but any data manipulation may force you to use the UPPER function
> to get the results you need.
>
> --
> SuperDave
> ------------------------------------------------------------------------
> SuperDave's Profile: http://www.excelforum.com/member.php...o&userid=25935
> View this thread: http://www.excelforum.com/showthread...hreadid=386752

--

Dave Peterson