Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Changing Case

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I need to convert text strings to proper case - ie first letter in each word in upper case, and all the remaining ones in lowercase. I can do the same with @Proper(A1) for single cell. Is there a way out to convert the whole range instead of converting one cell at a time.

Would a macro assist and how to go about.

Regards


Similar Excel Video Tutorials

Helpful Excel Macros

Capitalize the First Letter of Every Word in a Cell
- This macro will make the text of any selected cell in excel proper case. This means that the first letter of any word in
Convert Numeric Dollar Values into Text in Excel - UDF
- Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra
Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
Change Text to Lowercase
- This macro will change all text within the selected cells to lowercase. It works only on selected cells within Microsoft
Extract the First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
- This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first word f

Similar Topics







I got this macro from some NG some where (Thanks), what I need help on is making the macro ignore all characters in brackets "()".
I am absolutely dying trying to mod this macro.
Heck, is it even possible?

Code:

'Change Text to Upper Case or Proper Case. See Also:
'Force Upper Case/Proper Case

'Excel has 2 built in functions for converting text to
'either UPPER CASE or Proper Case. The 2 functions that
'do this are shown below;

'=UPPER(A1)
'=PROPER(A1)

'These Excel functions work well when referring to cells
'that house the text. However, there are many instances
'when using the Worksheet Function approach is not practical.
'The Excel macro code below can be used to change existing
'text to either UPPER CASE or Proper Case. If you run the
'macro with only a single cell selected it will work on the
'entire Worksheet. If you run the macro with more than 1
'cell selected it will work on only your selection.
'The other settings that the StrConv Function take are
'shown below. See the Excel VBA help for specifics.

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, "OzGrid.com")
    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


As always, I value all help provided.

To the MOD who improved my title, Thanks!


I am a basic VB user at best:

That being said. I'm running the following macro which converts case to upper or proper depending on the msg box selection. For some reason when I select only one cell and run the macro, it is changing the entire worksheet, not my selection. If I choose more than one cell and run the macro, it is working fine. This is in 2007 and 2003 both (I have users with both versions). Can anyone explain or provide a fix? Thank You!

Sub ConvertCase()
Dim rAcells As Range, rLoopCells As Range
Dim lReply As Long

Set rAcells = Selection

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, "website.com")
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


Excel has UPPER, LOWER and PROPER functions to convert the text to upper case, lower case or proper case. I know that VBA has UCase and LCase in place of UPPER and LOWER functions. Is there an equivalent in VBA for PROPER function?


Hi all,

Pl suggest how to convert a given text string to Proper Case by VB code.
VB Code of Upper & Lower Case exsist but couldn't trace for the Proper Case, as we have it in Normal Excel Formula bar.

Thanks & Regards,

Vijay Anand.R




The function PROPER() works well for changing single cells to the proper
case. What I need is to convert the entire spreadsheet, or specific columns
or rows, to the proper case.
That is, if column C has some or all names in a variety of cases, I'd like
to change that to column C with all proper. Sort of a PROPER(Column C).
Same for row 12 to be PROPER(Row 12).
I'm getting data that is mainly uppercase which includes the state ID, such
as TX for Texas, etc. I don't want this to be made proper. But the names,
cities, addresses, and some descriptions in the data, I'd like to convert to
proper format.
Does there exist a utility or a way to structure the PROPER function so I
don't have to do each cell?
TIA,
Tom



This single line of code given to me by MrKowz simply changes the current selected cell into proper case instead of whatever case format it's in. What would be the code to make it change a whole range of cells into lower case?

Actually, I have another question. Is there a case format to avoid the problem that:

BLA BLA BLA turns into Bla Bla Bla instead of Bla bla bla? In other words, it capitalizes the first letter of each word instead of just the first letter of the first word. If this could be fixed that would be great!

Code:

Sub Macro3()
  '
  ' Macro3 Macro
  '
  '
   ActiveCell.Value = Application.Proper(ActiveCell.Value)
   
  End Sub





I have a column of text; some cells have all upper case letters. I want all cells to have First letter only upper case, the rest in lower case.
I tried the PROPER function but am doing it wrong. I highlight the entire column, select PROPER, put the cells locations in the blank field (e.g., D2:D175), hit OK, nothing, except first cell now says PROPER. Please help.


Hi there,

I have a spreadsheet that is downloaded from a network. The names in column C are in upper case and I want to use the "Proper" function to convert to upper and lower case.

Each time I use this function (in column D) it produces the words "=PROPER(C)". Column D is formatted as text. Does anyone know why this occurs?

Thank you.


Is there a way to convert what I have in all Uppercase to sentence case? I have used the Proper function but it capitalizes the first letter of each word.

Extract in Upper case: THIS IS A TEST.
Proper Function: This Is A Test.
What I would like: This is a test.

Thanks for the help.


Hello,

Im really hoping someone can help me. I have created a text convertor which will convert any text into upper case or lowercase (not very complex but very handy!) Is there anyway way that when text is in uppercase and I convert it to lower case that it makes every character after a full stop in upper case

Example
DAVID WAS NOT WELL. HE WENT HOME
to
David was not well. He went home

I currently get
david was not well. he went home.

Any help would be fantastic! Thank you


Is there a way to highlight a portion of a phrase in an Excel cell and
convert the phrase to capital letters (lower case to upper case), rather
than retype the entire phrase? Also is it possible to highlight a portion
phrase and have all the first letters of words convert from lower case to
upper case? I have to go through an Excel document and convert sections of
phrases to all upper case letters, I would rather not retype the entire
section. I do not believe a formula will work as a portion of the cell must
be all caps and a portion lower case or proper, and because the data has
already been inputted.

Thanks, Bob





Hi there I know the PROPER function will convert all text to capitalise each word, is there a formula that can convert only the first letter to caps and the rest to lower case?

Thanks

Simon


Hi folks,

I'm a complete novice with VBA (never done any coding before). I have a list of customers within excel but all the font is in both upper case and lower case.

I need to use some coding to change the font to proper case in the cell range B2:J535 (i.e. all columns from B-J and all rows from 2-535)

Is this possible? and if so can anyone shed any light on where to start? please put your responses in complete novice format because I am trying to start from scratch having never seen VBA before.

Any assistance would be very much appreciated.

Darlo


Hi All,
I'm trying to find a way to test case of certain text-strings to see if they
are upper, lower, proper, etc., in a VBA in Excel (similar to Word's VBA
Case functions (wdLowerCase, wdUpperCase, wdTitleSentence, wdTitleWord) - it
would be nice to have Excel functions: IsTextUpper, IsTextLower,
IsTextProper, etc.
Does anyone know how this may be done?
Thanks a lot - JS





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.

Code:

 
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


Hi there,

A few questions:
1) how to convert a column [or row] of data into upper case?

2) how to merge two columns of names into 1 column where a space separates the two names

3) how to convert the names in a column to mixed case, that is, the first letter of a name is in upper case, the remainder lower-case? Hmm how to work with names like McCain or O'Hanlon, or Smith-Jones?

Would a book like 'excel for dunmmies' help here, or please recommend your gav book.

Thanks for the assist. I hope I can help others soon too!


Hi There,
I am using the "Proper" function to convert customer names from upper case (in column C) to an upper & lower case format (in column D). The code I use is

Range("D2").Select
ActiveCell.FormulaR1C1 = "=PROPER(RC[-1])"

What code can I use to fill column D to the bottom of the data? The number of rows will vary each time.

Please help?

Thanks.


Is it possible to write a macro to have a special search and replace that takes the replacement text and changes it's case to match the text in the destination cell.

i.e.
If the text in the destination cell is all lower case then the replacement string will all be lowercase. Similarily with uppercase.

If the destination cell contains both upper and lowercase then the replacement text would be in proper case.

Thanks!


All,

Maybe this isn't possible, I have looked around and I keep finding this method:

VB:

Private Sub Worksheet_Change(ByVal Target As Range) 
    With Target 
        If Not .HasFormula Then 
            .Value = UCase(.Value) 
        End If 
    End With 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



However, the column I am working in (Column H) is limited in size, thus I have typed information in the cell, upper cased it and then found out my narrative was too long for the cell, after it was converted to Upper Case.

I can simply hit, Caps Lock, but this needs to be dummy proof for others. We have had others fill out the form, all in lower case or proper case and then I had to go in and edit the entryies. So I thought, take the problem out, auto convert the text.

The above will (sometimes) convert the text after you leave the cell, this is close to what I need, but would rather know how much room I have as I type.

Ideas? Thanks in Advance.

I'm trying to figure out if there is any way of performing a logical check (possibly by way of a custom function?) to ascertain whether a cell contains a text string (no numbers) and is all in upper case (or lower case, or Proper Case).

The reason for this is that I receive approx 10,000 rows of data each month French customers who - for some strange reason - seem unable to tell the difference between a "First Name" and "Surname" field header (in French obviously!)

While most people do complete their data correctly, there is a large proportion where I can tell that the surname has been typed in the First Name field and vice versa. I can tell this because in France it is common practice to type your first name in proper case and your surname/family name in upper case.

What I therefore need is some way of identifying rows where data in Column A (First Name) is in upper case AND where the corresponding data in Column B (Surname) is in lower case or proper case.

Once identified, I can swap the values between columns.

Is there a relatively simple way of creating a logical check (to return TRUE or FALSE) on whether a cell's contents are in upper case, lower case or proper case?


I need to convert numerous cells scattered about a sheet to title case. I can place a formula with the PROPER function in a spare cell and then Paste Special the Values back into the original cell but that is cumbersome. I need a macro that converts the contents of the cell in one go. Can someone help please?


Hello, Could some one please help me with the following:

I have an Excel 2003 worksheet with all the data in it in Upper-case.

I found this VBA code which works if you change a cell.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler:
    If Not Application.Intersect(Me.Range("A1:U50000"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            'Target.Value = StrConv(Target.Text, vbLowerCase)
            'Target.Value = StrConv(Target.Text, vbUpperCase)
            Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If
ErrHandler:
    Application.EnableEvents = True
End Sub


Is there a way to automatically convert the entire worksheet from Upper-Case to Proper-Case using a macro please?

Kind regards,

Chris


How do I change a column of words that are all Upper Case to Proper case?



Hi
Is ther a way of setting a range to the 'proper' comand so tha no matter what case the user inputs the cells will return upper case on the first letter?


Hi Guys,

I've got the following code used to change text into Upper Case

Sub Uppercase()
' Loop to cycle through each cell in the specified range.
For Each x In Range("B1:B17")
' Change the text in the range to uppercase letters.
x.Value = UCase(x.Value)
Next
End Sub

Two questions.

1) How can I amend this code so I can change text in columns to Proper Case. I know LCase is Lower but PCase isn't valid (I did try)

2) Rather than specify the range in the VBA text I would like to have a pop-up box which will ask which column needs changing and once that has been entered the macro to update all used cells within that column. How would I do that?

Cheers
Jon