excel formatting

  kimtrnc 08:04 16 Apr 2009

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 2009

Are you trying to change all text to capitals?

  VoG II 08:17 16 Apr 2009

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 2009

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 2009

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 2009

Excel 2000...

  kimtrnc 05:15 17 Apr 2009

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 2009

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

Are the names in column A?

  kimtrnc 09:07 17 Apr 2009

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 2009

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 2009


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.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?