Is there an eaiser way exclude words commonly foun in address like DR, ST,
1ST from a proper formula Excel 2007

Easier than what?

"Geoffrey Starr" wrote in message
...
Is there an eaiser way exclude words commonly foun in address like DR, ST,
1ST from a proper formula Excel 2007

Hi Tyro,
You can use a macro, to make corrections such as on my
last name ("McRitchie"), but I don't see why you would
want to do anything different about "Dr.", and "St".

But the lower case words in titles such as "Wind in the Willows" and
other things can be tweaked in a macro.
http://www.mvps.org/dmcritchie/excel/proper.htm

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Tyro" wrote in message ...
Easier than what?

"Geoffrey Starr" wrote in message
...
Is there an easier way exclude words commonly found in address like DR, ST,
1ST from a proper formula Excel 2007

I don't want to do anything. I didn't ask the question.

Tyro

"David McRitchie" wrote in message
...
Hi Tyro,
You can use a macro, to make corrections such as on my
last name ("McRitchie"), but I don't see why you would
want to do anything different about "Dr.", and "St".

But the lower case words in titles such as "Wind in the Willows" and
other things can be tweaked in a macro.
http://www.mvps.org/dmcritchie/excel/proper.htm

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Tyro" wrote in message
...
Easier than what?

"Geoffrey Starr" wrote in
message ...
Is there an easier way exclude words commonly found in address like DR,
ST,
1ST from a proper formula Excel 2007

Sorry about that should have been addressed to Geoffrey.

"Tyro" wrote...
I don't want to do anything. I didn't ask the question.

The change to properwas with a Address in all Caps like :

2ND VALLEY RD <-- run thru =TRIM(CLEAN(PPROPER(value)))

OR

DBA JOHN SMITH

Comes out as:

2Nd Valley Rd

Or

Dba John Smith

so you need to have an second Column with a
=SUBSTITUTE(Value,"2Nd","2nd",1) and another with

=SUBSTITUTE(value,"Dba","DBA",1)

wich can result in building the sheet taking a while

That is why I suggest you use a macro
http://www.mvps.org/dmcritchie/excel/proper.htm

You will have to modify the the macros Proper_Case_Inner and also CapWords
and you invoke from the macro Proper_case

2nd Valley Rd <-- Run Thru =trim(clean(proper(value)))
Dba John Smith

The VBA equivalent of PROPER does not capitalize the N as in 2nd
You can easily fix DBA as a word by adding that to the CapWords macro.
Modify the macros for your own data experience.

I would avoid use of CLEAN
Removing Unprintable Characters (#removeunprintable)
http://www.mvps.org/dmcritchie/excel...oveunprintable

You can accomplish what you want by running the
TrimALL macro followed by Proper_case macro on the same selection
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
http://www.mvps.org/dmcritchie/excel/proper.htm


--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Geoffrey Starr" wrote in message
...
The change to properwas with a Address in all Caps like :

2ND VALLEY RD <-- run thru =TRIM(CLEAN(PPROPER(value)))

OR

DBA JOHN SMITH

Comes out as:

2Nd Valley Rd

Or

Dba John Smith

so you need to have an second Column with a
=SUBSTITUTE(Value,"2Nd","2nd",1) and another with

=SUBSTITUTE(value,"Dba","DBA",1)

wich can result in building the sheet taking a while