excel formatting

  kimtrnc 08:04 16 Apr 09

I have just taken on a voluntary task for a local private library. Previous guardian typed author's surname in caps, and first name in "sentence" mode (ie first letter of each name CAPS and others small).
I changed this to "all caps" as it makes entering data approx 4 times as quick if I don't have to keep shifting.
I have a wonderful add-in called ASAP utilities, but nowhere can it change a cell to "FIRST WORD CAPS, Rest Of Name,Sentence Mode)
I bet there is a guru here who knows how to do this, pretty please......

  OTT_Buzzard 08:14 16 Apr 09

Are you trying to change all text to capitals?

  VoG II 08:17 16 Apr 09

Assuming that the names are in the format SMITH FRED entered in column A try this. Press ALT + F11 to open the Visual Basic Editor, Insert > Module and paste the following into the white space on the right

Sub Change()
Dim LR As Long, i As Long, X As Variant
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("A" & i)
X = Split(.Value)
.Value = X(0) & " " & WorksheetFunction.Proper(X(1))
End With
Next i
End Sub

Press ALT + F11 to return to your sheet, Tools > Macro > Macros > highlight Change and click the Run button.

  kimtrnc 15:00 16 Apr 09

VoG - thanks for that i'm not into VB
Allgoes well until I try to run the macro - then it tells me "compile error sub or function" and highlights 'Split'
Any more ideas, please?

  VoG II 15:29 16 Apr 09

You must have a fairly old version of Excel (I'm not sure exactly when Split was introduced).

Try replacing that code with this:

Sub Change()
Dim LR As Long, i As Long, P As Integer
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("A" & i)
P = InStr(.Value, " ")
.Value = Left(.Value, P - 1) & " " & WorksheetFunction.Proper(Right(.Value, Len(.Value) - P))
End With
Next i
End Sub

  OTT_Buzzard 15:37 16 Apr 09

Excel 2000...

  kimtrnc 05:15 17 Apr 09

Sorry again - followed your instructions to the letter,(& erased the previous macro) but it still won't work.
I am using Excel 97 - yeah, know it's old but I really prefer Office97. If I have to upgrade, I suppose I will....

  VoG II 08:02 17 Apr 09

What happens - nothing, error, unexpected result, ...

Are the names in column A?

  kimtrnc 09:07 17 Apr 09

VoG nothing happens.
When looking at the code in debugger it says
runtime error '5'invalid procedure call or argument.
Debug highlights this:
.Value = Left(.Value, P - 1) & " " & WorksheetFunction.Proper(Right(.Value, Len(.Value) - P))

I do appreciate your helping me!

  OTT_Buzzard 09:11 17 Apr 09

Thanks for pointing out the cross-forum posting the other day. I think the users problems have now been solved....

kimtrnc: sorry to distract from your question!

  VoG II 09:40 17 Apr 09


Try replacing that line with

.Value = Left(.Value, P) & StrConv(Right(.Value, Len(.Value) - P), vbProperCase)

If that doesn't work then I'm baffled (I have tested the code, as usual, before posting it).

If you wish you can click my yellow envelope to send me a personal message - I'll reply and you can then e-mail the workbook to me. However, I can only test in Excel 2000 and 2007.

This thread is now locked and can not be replied to.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now