Changing letter case to user specified.
 

I have a list of names in both WORD and EXCEL and would like to change the
case letters to look like this...

LAST, First

How can I do this? Is there a function I could use in EXCEL or WORD?

Joe

I hate to bring bad news but I don't believe you can do this. However when
using names you should probably have a separate column for first and last
name. This would make it really easy to accomplish what you are doing.

"JoeWood77" wrote:

I have a list of names in both WORD and EXCEL and would like to change the
case letters to look like this...

LAST, First

How can I do this? Is there a function I could use in EXCEL or WORD?

Joe

In your list of names, are the first and last names in separate columns? If
they are in one column, try this:

=UPPER(LEFT(A1,FIND(",",A1)))&PROPER(MID(A1,FIND(" ,",A1)+1,99))

If they are in two and you want them in one, try this

=UPPER(A1)&", "&PROPER(B1)

Hope this helps.

Jane

"JoeWood77" wrote:

I have a list of names in both WORD and EXCEL and would like to change the
case letters to look like this...

LAST, First

How can I do this? Is there a function I could use in EXCEL or WORD?

Joe

Hi Joe,

The default if there is no comma will be proper case, since
I think that would be an error wouldn't know which you would prefer.

Sub Surname_Case()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, i As Long
On Error Resume Next 'In case no cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Formula = Application.Proper(cell.Formula)
i = InStr(1, cell.Formula, ",")
If i 0 Then cell.Formula = Left(UCase(cell.Formula), i - 1) _
& Mid(cell.Formula, i)
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

The above is a macro, if not familiar with macro see
http://www.mvps.org/dmcritchie/excel/getstarted.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"JoeWood77" wrote in message ...
I have a list of names in both WORD and EXCEL and would like to change the
case letters to look like this...

LAST, First

How can I do this? Is there a function I could use in EXCEL or WORD?

Joe

Here's one way to use Excel: with LAST, first in cell A1, use this
formula in another cell:
=UPPER(MID(A1,1,FIND(",",A1,1)))&MID(A1,FIND(",",A 1,1)+1,LEN(A1))

This formula converts anything before the comma to upper case, and
everything after the comma as it originally appears.

Hi Joe,
This is something commonly done in telephone directory
listings, and I am surprised that I've not seen this request
before in the newsgroups.

Macro solutions do not require any additional effort on
your part to remove introduced formulas, because they
will do the work in place on a selection.

Here is another macro solution involving three macros,
which will work much better for you. I have no idea what
you can do in Microsoft Word to implement this, but this
is for Excel using macros.

I changed my mind on the no comma part, since the
list will be sorted, will assume that no comma means a
last name only, and not an error.

The Surname_case macro calls another macro to
actually do the work but this is what it will do for
a selection when you invoke the macro.

Up through the comma would be boldface and upper case,
after the comma would be regular and proper case.

If there is no comma you will get boldface and upper case
for the entire cell. Although it would be easy to change
how this works.

I repeat just so this article is complete, If not familiar with
macros install the following in a regular macro, see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Sub Surname_Case()
'-- This macro is invoked by you -- i.e. from Macro Dialog (Alt+F8)
Surname_Case_Inner 'The macro you invoke from a menu is Proper_Case
End Sub
Sub Surname_Case_Inner(Optional mySelection As String)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, i As Long, rng As Range
'On Error Resume Next 'In case no cells in selection
If mySelection = "" Then
Set rng = Selection
Else
Set rng = Range(mySelection)
End If
For Each cell In Intersect(rng, _
rng.SpecialCells(xlConstants, xlTextValues))
cell.Formula = UCase(cell.Formula)
i = InStr(1, cell.Formula, ",")
If i = 0 Then
cell.Font.FontStyle = "Bold"
Else
cell.Formula = Left(cell.Formula, i) _
& Application.Proper(Mid(cell.Formula, i + 1))
With cell.Font
.FontStyle = "Bold"
End With
With cell.Characters(Start:=i + 1).Font
.FontStyle = "Regular"
End With
End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Now after running the above macro, you might want changes
to column 2 (col B) to be automatic. You would use an Event
macro for this, which is installed differently. You can read
about them later in
http://www.mvps.org/dmcritchie/excel/event.htm

Right click on the worksheet tab, view code, plop this in
and it will ONLY apply to this worksheet. My assumption
is that you have a phone number in column A, the name
in Column B, additional information in remaining columns.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.row = 1 Then Exit Sub 'don't override headings in row 1
If Target.Column < 2 Then Exit Sub 'only allow changes to Col B
Application.EnableEvents = False
Application.Run "pesonal.xls!Surname_Case_inner", Target.Address
Application.EnableEvents = True
End Sub

For more information on how these macros work and why you
would NOT want to place additional worksheet functions
on the worksheet as in the Excel only solutions provided
in some of the other responses, see
Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm

Once you've installed and used these macros, you should
be aware that the newsgroups
for working with macro solutions is excel.programming
where it will be assumed you know how to install and use
a macro.

Some people manage to do all of the above the first time
in fifteen minutes, but I think an hour of your time is more reasonable
for the first time installing and using macros and well worth the time spent.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"JoeWood77" wrote ...
I have a list of names in both WORD and EXCEL and would like to change the
case letters to look like this...

LAST, First

How can I do this? Is there a function I could use in EXCEL or WORD?

Joe

"Dave O" wrote:

Here's one way to use Excel: with LAST, first in cell A1, use this
formula in another cell:
=UPPER(MID(A1,1,FIND(",",A1,1)))&MID(A1,FIND(",",A 1,1)+1,LEN(A1))

This formula converts anything before the comma to upper case, and
everything after the comma as it originally appears.

Thank you so much! This is what I was looking for!

"David McRitchie" wrote:

Hi Joe,

The default if there is no comma will be proper case, since
I think that would be an error wouldn't know which you would prefer.

Sub Surname_Case()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, i As Long
On Error Resume Next 'In case no cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Formula = Application.Proper(cell.Formula)
i = InStr(1, cell.Formula, ",")
If i 0 Then cell.Formula = Left(UCase(cell.Formula), i - 1) _
& Mid(cell.Formula, i)
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

The above is a macro, if not familiar with macro see
http://www.mvps.org/dmcritchie/excel/getstarted.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"JoeWood77" wrote in message ...
I have a list of names in both WORD and EXCEL and would like to change the
case letters to look like this...

LAST, First

How can I do this? Is there a function I could use in EXCEL or WORD?

Joe


Thank you for all of your help!!

Joe

Hi Joe,
I see you chose the Worksheet Functions solutions for now, but just to let you
know I did put this on to a web page, so that you can see much better what
was done and how it works with macros *in Excel*.

Surnames, Special formatting for LASTNAME, firstname
http://www.mvps.org/dmcritchie/excel/surname.htm

I can't tell what your reply is -- the entire reply looks like quoted material..
If you were entering a CLOSED instruction for the thread, most
regulars here including MVP's would never see that because we don't
use the CDO (Communities web interface). I use Outlook Express myself..
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Joewood77" wrote [clipped...]

Changing letter case to user specified.
 

Converting the full names in column A, formatted "LastName, FirstName" into
names formatted "FirstName LastName".

Solution:

Use the RIGHT, LEFT, LEN and FIND text category functions, as follows:
=RIGHT(A2,LEN(A2)-FIND("","",A2)-1)&"" ""&LEFT(A2,FIND("","",A2)-1)

Example:

Last Name, First Name Result
Seinfeld, Jerry Jerry Seinfeld
Bush, George George Bush
Jordan, Michael Michael Jordan
Bowie, David David Bowie


"JoeWood77" wrote:

I have a list of names in both WORD and EXCEL and would like to change the
case letters to look like this...

LAST, First

How can I do this? Is there a function I could use in EXCEL or WORD?

Joe