G
Geoffrey Starr
Is there an eaiser way exclude words commonly foun in address like DR, ST,
1ST from a proper formula Excel 2007
1ST from a proper formula Excel 2007
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
T
Tyro
Easier than what?
D
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
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
T
Tyro
I don't want to do anything. I didn't ask the question.
Tyro
Tyro
D
David McRitchie
Sorry about that should have been addressed to Geoffrey.
...
...
G
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
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
D
David McRitchie
That is why I suggest you use a macro
You will have to modify the the macros Proper_Case_Inner and also CapWords
and you invoke from the macro Proper_case
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
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