Formatting Cell Using Formulae....advise please....

[Guest]

I am trynig to understand how to format a column to use the "Proper" argument.
The help example appears to be for one cell only I whould like say all first
names and second names to start with capitals. I can't seem to set this up. I
end up with either the formula showing in one cell or a circular error. I
don't think I have to format each cell individually in a column?

I would also like to know how to format a column so the when I press the
letter Y or N it enters Yes or No as typed here?

Any other suggestions how I can get to grips with the formatting ffeatures
would be appreciated.

 

[Bob Phillips]

On the first point, you would enter the PROPER formula in an adjacent cell,
then copy down for all rows.

The second would require VBA.


Formatting does not change the content, just its appearance, so it will not
make ordinary text, proper case text, nor change Y to Yes.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob,
Quote
The second would require VBA.

I have looked for some code to do this bob.....
could you suggest some, thanks
Dermot

 

[Guest]

Hi Bob,
I do understand what the Proper function does now, but it does not do for me
what I thought it would to do.

Initial Question Reworded
What is the most commonly used way to format text in a spreadsheet for best
readability and appearance?

How would I achieve this?

Thanks
Dermot

 

[David McRitchie]

Hi Dermot,
The best way to fill out a spreadsheet is to fill it out correctly the first time.
I can be as ambiguous as you are.

Now then since I think you are still looking to use Proper Case (or Title Case)
you are talking about formatting and it is not a format in Excel.

Also a function (formula if you like) cannot change the formatting of any cell including itself.
And you do not want a function referring to it's own value as that would be a
circular reference and very few aplications work that very well. Like a dog chasing it's tail.

In order to use the PROPER Worksheet Function you would need to use
a helper column (meaning another column with formulas). This can get
messy as your intent is to change the original column.

So you would be much better off making the corrections to your column
at one shot with a macro. The Proper_case macro on my page
will ignore formulas but will handle Text constants quite well.
http://www.mvps.org/dmcritchie/excel/proper.htm
You will also see a description of why you would not want to use a formula
to make the change.

Microsoft Word, I've read, allows you to format as proper, but not Excel.

 

[Guest]

Thank you very much for the reply and links David.

I did not intend to be ambiguous, but when I read over my question again it
is very vague. Being a beginner to excel, and excel being complex I just want
to ensure I learn good methods of presentation as well as content in the
speadsheets I make up. I have seen some complex spreadsheets by some people
and to me they are awful to look at.

Thanks again for your time and explanations
Dermot

 

[David McRitchie]

Hi Dermot,
Thanks, Use of newsgroups certainly helps learn English even for those whose
only language is English, and it helps to improve making oneself clear.

Along the lines of what you were originally trying to you might look
at use of the Fillhandle and the mouse at
http://www.mvps.org/dmcritchie/excel/fillhand.htm
but as indicated before you want to use a macro for this.

Also look at the Tutorials at
http://www.mvps.org/dmcritchie/excel/excel.htm#tutorials
among which I would suggest looking at the Data Pig Technologies
Flash presentations. They are just a few minutes each and Mike
Alexander did a very good job on them.

As far as recognizing decent macros from newsgroups and in
writing your own I would suggest looking over
http://www.mvps.org/dmcritchie/excel/proper.htm
http://www.mvps.org/dmcritchie/excel/slowresp.htm

 

[Guest]

Hi David,
That was an unexpected bonus, thanks for these links.
Much appreciated
Dermot