How to format the value in a cell?

  • Thread starter Thread starter An
  • Start date Start date
A

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.
 
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")
 
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
 
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?
 
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.
 
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
 
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
 
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
 
I agree, the inability to "force" a case setting in Excel data entry i
a problem. :mad: In Canada, postal codes are in the format A1B 2C3
while uppercase letters are not strictly required, it looks odd if the
are not used. Things would be so much simpler if I could use a tex
format on a cell to "force" the characters to be uppercase, instead o
having to use a macro (which the user may choose to disable) or
function (which is a "kludge" solution). Microsoft, are yo
listening?

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

You may want to send your request to:
(e-mail address removed)
 
Back
Top