Eloise T
Active Member
I have been pestering Microsoft Excel to put CHANGE CASE (exactly like what is in Word) in MS Excel...so far to no avail. Obviously, I have been pestering the MS We-Don't-Care-What-Your-Suggestion-May-Be---Go-Away dept.
My problem: I have spreadsheets sent to me weekly in which I need to change y and n to Y and N before I forward them.
The following VBA was sent to me by an unknown friend.
I'm able to install the TOGGLECASE function but, ...
1) I don't know how to use it.
2) I don't think it will do any better than the already existing UPPER, PROPER, or LOWER functions which means I would have to create special columns to pull the data from the existing columns, and then Copy and Paste Special the corrected data back to the original columns, correct?
Function TOGGLECASE(Text As String) As String
Dim x As Long
Dim Character As String
Dim NewTextArray As Variant
ReDim NewTextArray(1 To Len(Text))
For x = 1 To Len(Text)
Character = Mid$(Text, x, 1)
Select Case Character
Case "A" To "Z"
NewTextArray(x) = LCase$(Character)
Case "a" To "z"
NewTextArray(x) = UCase$(Character)
Case Else
NewTextArray(x) = Character
End Select
Next x
TOGGLECASE = Join(NewTextArray, vbNullString)
End Function
My problem: I have spreadsheets sent to me weekly in which I need to change y and n to Y and N before I forward them.
The following VBA was sent to me by an unknown friend.
I'm able to install the TOGGLECASE function but, ...
1) I don't know how to use it.
2) I don't think it will do any better than the already existing UPPER, PROPER, or LOWER functions which means I would have to create special columns to pull the data from the existing columns, and then Copy and Paste Special the corrected data back to the original columns, correct?
Function TOGGLECASE(Text As String) As String
Dim x As Long
Dim Character As String
Dim NewTextArray As Variant
ReDim NewTextArray(1 To Len(Text))
For x = 1 To Len(Text)
Character = Mid$(Text, x, 1)
Select Case Character
Case "A" To "Z"
NewTextArray(x) = LCase$(Character)
Case "a" To "z"
NewTextArray(x) = UCase$(Character)
Case Else
NewTextArray(x) = Character
End Select
Next x
TOGGLECASE = Join(NewTextArray, vbNullString)
End Function