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