PROPER Formula Question

[Geoffrey Starr]

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

 

[Tyro]

Easier than what?

 

[David McRitchie]

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

 

[Tyro]

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

Tyro

 

[David McRitchie]

Sorry about that should have been addressed to Geoffrey.

...

 

[Geoffrey Starr]

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

 

[David McRitchie]

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/strings.htm#removeunprintable

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