Change Text to Upper Case Query

[paulstan]

I have used the following code (from OzGrid Website) to convert selected text to UPPER case, which works just fine.

This conversion to UPPER text will be applied to a customer's name cell (containing both Surname and Forename), ie STANLEY PAUL

When the conversion has been done I would also like the Macro to insert a comma and a space in between the Surname and Forename ie STANLEY, PAUL This would be handy if the cells were ever split into separate Forename and Surname cells.

Sub ConvertCase()
Dim rAcells As Range, rLoopCells As Range
Dim lReply As Long
    'Set variable to needed cells
    If Selection.Cells.Count = 1 Then
        Set rAcells = ActiveSheet.UsedRange
    Else
       Set rAcells = Selection
    End If
 
    On Error Resume Next 'In case of NO text constants.
    'Set variable to all text constants
    Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)
    If rAcells Is Nothing Then
       MsgBox "Could not find any text."
       On Error GoTo 0
       Exit Sub
    End If
    lReply = MsgBox("Select 'Yes' for UPPER CASE or 'No' for Proper Case.", vbYesNoCancel, "")
    If lReply = vbCancel Then Exit Sub
 
    If lReply = vbYes Then ' Convert to Upper Case
          For Each rLoopCells In rAcells
              rLoopCells = StrConv(rLoopCells, vbUpperCase)
          Next rLoopCells
    Else ' Convert to Proper Case
          For Each rLoopCells In rAcells
              rLoopCells = StrConv(rLoopCells, vbProperCase)
          Next rLoopCells
    End If
 
End Sub

Regards

Paul S

 

[taurean]

Although you can do it by macro. You can also do it by following formulas if need be:
Suppose you have STANLEY PAUL in cell A1 then:
=LEFT(A1,FIND(" ",A1,1)-1)
=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

Also you can implement Text to Columns

Will you still need a macro?

 

[Scott Huish]

Very simple formula:

Excel Workbook
	A	B
1	Stanley Paul	STANLEY, PAUL
Sheet1

 

[paulstan]

Thanks for the replies.

Yes, it will still need to be run as a macro as its for an end-user.

Regards

Paul S

 

[Scott Huish]

Sub test()
Dim c As Range
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    c = UCase(Replace(c, " ", ", ", , 1))
Next
End Sub

 

[paulstan]

I guess that's why they are called MVPs!

Problem SOLVED and many thanks for everyone's assistance.

Regards

Paul S

 

[paulstan]

Many, many thanks for all the help. I thought the Thread was SOLVED but, hey presto, the end-user has now added another twist to the story.

As well as the name being converted to UPPER case with a comma, they also want the Forename and Surname swapping round.

Example John Smith will need to be shown as SMITH, JOHN

And yet another twist(!!) - there are instances where a person has 3 names (not double-barrelled either)

Example John Von Smith would need to be shown as VON SMITH, JOHN

I still need all this to be run from a single macro.

Regards

Paul S

Code in use below:

'Convert text to UPPer case
Sub Comma_UPPER()
Application.ScreenUpdating = False
Dim c As Range
For Each c In Range("E5:E" & Range("E" & Rows.Count).End(xlUp).Row)
    c = UCase(Replace(c, " ", ", ", , 1))
Next
Application.ScreenUpdating = True
End Sub

 

[taurean]

If the data is consistent then you can use following code.

Public Sub Comma_UPPER()
Application.ScreenUpdating = False
Dim c As Range
Dim vName As Variant
For Each c In Range("E5:E" & Range("E" & Rows.Count).End(xlUp).Row)
    vName = Split(c.Value2, " ")
    Select Case UBound(vName)
    Case 0
    c = UCase(c)
    Case 1
    c = UCase(vName(1) & " , " & vName(0))
    Case 2
    c = UCase(vName(1) & " " & vName(2) & " , " & vName(0))
    Case Else
    c.Font.Color = vbRed
    c.Interior.Color = vbYellow
    End Select
Next
Application.ScreenUpdating = True
End Sub

I have specifically used 'Select Case' so that you can add more conditions like 4 named condition (I don't know if that happens). The code will highlight cells where it has not altered data (Case Else).

 

[paulstan]

Dear Shrivallabha

Many thanks for your reply. The code works like a dream.

Can I safely say this thread has been SOLVED?

Regards all

Paul S