Format portion of formula

I have a person's name. Current format is last name space first name, all in
caps. What I need to do is separate the first and last name, add a comma and
space after the last name then have the first name all in one cell. This is
done, no problems to this point. My problem lies with formatting. I have
been asked to have the first letter of the last and first name in caps and
the rest in lower case. In addition, the full last name needs to be in bold
while the rest of the cell contents are in regular format. I am not real
familiar with the macro world, but I'm not scared of it either if it will
make it easier (it will just have to be spelled out). Formulas I can work
with readily. Can anyone please help with getting this done?

You can make the first letters capitalized by using the PROPER formula. As
for bolding only the last name, I don't know of a formula for that-- would
probably have to be a macro, and is beyond my knowhow.
-Ed

"JC" wrote:
> I have a person's name. Current format is last name space first name, all in
> caps. What I need to do is separate the first and last name, add a comma and
> space after the last name then have the first name all in one cell. This is
> done, no problems to this point. My problem lies with formatting. I have
> been asked to have the first letter of the last and first name in caps and
> the rest in lower case. In addition, the full last name needs to be in bold
> while the rest of the cell contents are in regular format. I am not real
> familiar with the macro world, but I'm not scared of it either if it will
> make it easier (it will just have to be spelled out). Formulas I can work
> with readily. Can anyone please help with getting this done?
>

Hi,

Try this: insert macros into a general module (you can copy/paste code from
here) and then run the "Test" macro. Change the range as required.

You can invoke VB Editor by Alt+F11, then right click on VBAProject in the
Project window, do Insert-->Module and copy/paste this code.

Sub Test() ' test macro
' Reformat cell A1
call ReformatName(range("a1")) ' A! contains cell with name e.g "brown, john"
End


Sub ReFormatName(rng As Range)
If rng.Count = 1 Then ' Single cell
rng.Select
ActiveCell = Application.Proper(ActiveCell)
snl = InStr(1, ActiveCell, ",") - 1 ' Length of surname
ActiveCell.Characters(Start:=1, Length:=snl).Font.FontStyle = "Bold"
End If
End Sub

HTH

"JC" wrote:

> I have a person's name. Current format is last name space first name, all in
> caps. What I need to do is separate the first and last name, add a comma and
> space after the last name then have the first name all in one cell. This is
> done, no problems to this point. My problem lies with formatting. I have
> been asked to have the first letter of the last and first name in caps and
> the rest in lower case. In addition, the full last name needs to be in bold
> while the rest of the cell contents are in regular format. I am not real
> familiar with the macro world, but I'm not scared of it either if it will
> make it easier (it will just have to be spelled out). Formulas I can work
> with readily. Can anyone please help with getting this done?
>

This works for one cell. You mentioned I can change the range. How do I do
this? I want to start at cell "F2" and end where the text stops (this is not
a constant end point and will change every time, need to work something in to
select the "F" cells until there is a blank in the range). How do I modify
the macro to do this?

"Toppers" wrote:

> Hi,
>
> Try this: insert macros into a general module (you can copy/paste code from
> here) and then run the "Test" macro. Change the range as required.
>
> You can invoke VB Editor by Alt+F11, then right click on VBAProject in the
> Project window, do Insert-->Module and copy/paste this code.
>
> Sub Test() ' test macro
> ' Reformat cell A1
> call ReformatName(range("a1")) ' A! contains cell with name e.g "brown, john"
> End
>
>
> Sub ReFormatName(rng As Range)
> If rng.Count = 1 Then ' Single cell
> rng.Select
> ActiveCell = Application.Proper(ActiveCell)
> snl = InStr(1, ActiveCell, ",") - 1 ' Length of surname
> ActiveCell.Characters(Start:=1, Length:=snl).Font.FontStyle = "Bold"
> End If
> End Sub
>
> HTH
>
> "JC" wrote:
>
> > I have a person's name. Current format is last name space first name, all in
> > caps. What I need to do is separate the first and last name, add a comma and
> > space after the last name then have the first name all in one cell. This is
> > done, no problems to this point. My problem lies with formatting. I have
> > been asked to have the first letter of the last and first name in caps and
> > the rest in lower case. In addition, the full last name needs to be in bold
> > while the rest of the cell contents are in regular format. I am not real
> > familiar with the macro world, but I'm not scared of it either if it will
> > make it easier (it will just have to be spelled out). Formulas I can work
> > with readily. Can anyone please help with getting this done?
> >

Hi,

Change "Sheet1" to whatever your worksheet is called


Sub test()

Dim rng As Range, cell As Range

With Worksheets("Sheet1")

lastrow = Cells(Rows.Count, "F").End(xlUp).Row ' Find last non-blank row in
column F
Set rng = Range("F2:F" & lastrow) ' Set range to all cells from 2 to lastrow
For Each cell In rng ' loop through each cell in range
Call ReFormatName(cell)
Next cell

End With

End Sub


HTH

> This works for one cell. You mentioned I can change the range. How do I do
> this? I want to start at cell "F2" and end where the text stops (this is not
> a constant end point and will change every time, need to work something in to
> select the "F" cells until there is a blank in the range). How do I modify
> the macro to do this?
>
> "Toppers" wrote:
>
> > Hi,
> >
> > Try this: insert macros into a general module (you can copy/paste code from
> > here) and then run the "Test" macro. Change the range as required.
> >
> > You can invoke VB Editor by Alt+F11, then right click on VBAProject in the
> > Project window, do Insert-->Module and copy/paste this code.
> >
> > Sub Test() ' test macro
> > ' Reformat cell A1
> > call ReformatName(range("a1")) ' A! contains cell with name e.g "brown, john"
> > End
> >
> >
> > Sub ReFormatName(rng As Range)
> > If rng.Count = 1 Then ' Single cell
> > rng.Select
> > ActiveCell = Application.Proper(ActiveCell)
> > snl = InStr(1, ActiveCell, ",") - 1 ' Length of surname
> > ActiveCell.Characters(Start:=1, Length:=snl).Font.FontStyle = "Bold"
> > End If
> > End Sub
> >
> > HTH
> >
> > "JC" wrote:
> >
> > > I have a person's name. Current format is last name space first name, all in
> > > caps. What I need to do is separate the first and last name, add a comma and
> > > space after the last name then have the first name all in one cell. This is
> > > done, no problems to this point. My problem lies with formatting. I have
> > > been asked to have the first letter of the last and first name in caps and
> > > the rest in lower case. In addition, the full last name needs to be in bold
> > > while the rest of the cell contents are in regular format. I am not real
> > > familiar with the macro world, but I'm not scared of it either if it will
> > > make it easier (it will just have to be spelled out). Formulas I can work
> > > with readily. Can anyone please help with getting this done?
> > >