Excel case change (I *have* looked in Help!)

  exdragon 16:26 24 Feb 09
Locked

Hi - according to MS Help, I need to put the formula =PROPER(A2) if I want to change a column of uppercase names to title case - but where do I put it, please??

I've tried the example given and can switch between the formula and the name, but can't work it out.

Sorry to be so dumb.

  DippyGirl 16:47 24 Feb 09

In all the cells where you want the Name to appear.
So if A1 had JOHNNY DOE and you want Johnny Doe in D1 you put =(PROPER(A1)) in D1
If you want all the A cells in the D cells copy D1 to all the others

  exdragon 17:23 24 Feb 09

Ah - I think I may have got hold of the wrong end of the stick. My names are in column C and I thought I could just change them all in the same column, a bit like you can change case in Word.

  VoG II 17:24 24 Feb 09

If you want to replace the values in column A then press ALT + F11 to open the Visual Basic Editor, Insert > Module and paste in


Sub ToProper()
Dim i As Long, LR As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
Range("A" & i).Value = Application.Proper(Range("A" & i).Value)
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub



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

  VoG II 17:25 24 Feb 09

Having seen your last post:


Sub ToProper()
Dim i As Long, LR As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LR
Range("C" & i).Value = Application.Proper(Range("C" & i).Value)
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

  exdragon 23:59 24 Feb 09

I abase myself at your feet in humble adoration...

It worked, of course - many thanks!

  Taff™ 07:46 25 Feb 09

Us mere mortals use ASAP utilities (Free Version) which is a collection of useful tools. click here I`ve used it ever since VoG™ first mentioned it several years ago.

  exdragon 08:22 25 Feb 09

Taff™ - I used to use it too, until I didn't re-install it after a reformat. I believe it's no longer free and to be honest, I didn't use it that much. For the odd problem I have, it's generally possible to get the answer here.

  exdragon 14:35 27 Feb 09

Can I just apply a macro to a specific column?

I've got 2 columns (A & B) which show first and last names in the proper title case, but somehow, the next column, which shows their qualifications had ended up in the same way.

At first I thought it may have been because I'd inadvertently selected the entire worksheet, but in another column, some of the countries are showing as upper case while others are title case.

I amended VoG™'s macro to =ToUpper where necessary so should I be able to just highlight column C and get my capitals back?

Thanks!

  VoG II 14:44 27 Feb 09

This allows you to select the column (one at a time!) to convert to proper:

Sub ToProper()
Dim i As Long, LR As Long, col As Integer, r As Range
Set r = Application.InputBox("Click in the column to convert", Type:=8)
If r Is Nothing Then Exit Sub
col = r.Column
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
LR = Cells(Rows.Count, col).End(xlUp).Row
For i = 1 To LR
Cells(i, col).Value = Application.Proper(Cells(i, col).Value)
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Similarly this will convert your chosen column to uppercase.



Sub ToUpper()
Dim i As Long, LR As Long, col As Integer, r As Range
Set r = Application.InputBox("Click in the column to convert", Type:=8)
If r Is Nothing Then Exit Sub
col = r.Column
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
LR = Cells(Rows.Count, col).End(xlUp).Row
For i = 1 To LR
Cells(i, col).Value = UCase(Cells(i, col).Value)
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

  exdragon 14:56 27 Feb 09

Many thanks - I've been deeply embroiled in debugging and didn't have the faintest idea what I was doing!

Strange - I know the words are English, but all resemblance to the language I recognise seems to vanish in cases like this.

Just out of curiosity, if your first example worked by me changing the column letters to get the title case working why didn't it work when I changed propper to upper, having cut and pasted your macro? If it's too complicated, please ignore this question...

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

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

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

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…