Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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

View Answers     

Similar Excel Tutorials

Stop Excel from Changing Numbers to Dates or Anything Else
How to stop Excel from automatically changing numbers into anything other than what you typed, such as changing a f ...
Make All Text Upper Case or Lower Case in Excel
How to quickly change all text to upper case or lower case in Excel.  This allows you to change the case of text wi ...
Hide Data Within a Worksheet in Excel
In Excel you can actually hide data that is stored within a worksheet. This allows you to show data that is useful ...
Case Sensitive Sorting in Excel
How to perform case sensitive sorting in Excel. Select a cell within the data that you want to sort and then go to ...

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
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to

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!


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.


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 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


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.



Hello:

Please refer to attached sheet.
I have long list of names, I am showing you sample list in Sheet1

COlumn A and Column D i need help.
As you can see inconsistant with uuper and lower case letter.

I need VB Macro to convert all words starting with upper case and rest be lower case in column A and D.

Please let me know if any questions

Thank you.

Riz

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 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


Column B in a spreadsheet has a single word in each cell. Some words
begin with an upper case letter, others with a lower case letter. Can
someone please tell me a simple way to extract a list of those rows
where the word in column B begins with an upper case letter?




I have text in column A4:A300. The first letter in each is either in upper case or lower case. I want a macro or VBA code to turn the first letter in each cell in upper case with a single click.

Thanks.


Okay, I've had a lot of good help on the topic of changing certain entries to proper case. The current script I have with me is:

Code:

Sub Test()
Dim cl As Range
For Each cl In Selection
cl = UCase(Left(cl, 1)) & LCase(Right(cl, Len(cl) - 1))
Next cl
End Sub


It basically turns all the current selection to sentence case. Sentence case is the first letter of the first word capitalized, then the rest uncapitalized.

My question is if you can make exceptions where the word Bible or God, for example, would be capitalized and the rest normal.

Thanks!


I just bought a mailing list for my business and need to send a formal letter to each individual - unfortunately the letter is in all caps which doesn't make for a professional looking letter. Can anyone tell me how to format an entire column into proper case (each first letter capitalized and subsequent letters lower case) without having to put a separate formula into each cell? Thanks so much - this is driving me crazy -


I have found plenty of code to convert text in a range from UPPERCASE to Proper Case but I want to be able to exclude from the conversion any text that matches text found in a list on another worksheet (USA, TX, PD, UPS, a, an, at, in , on, etc.).

Can this be done referencing the list as a named range or would it be best to simply refer to the list of words by Sheet.Range?

Anyone have an example I could follow?

Ron


Hi

I'm using Change Event code to convert strings to Proper Case (using vbProperCase), which works fine changing "client name" to "Client Name".
However, where there is a string such as "client name (location)", it just converts to "Client Name (location)" - i.e. it doesn't change the first letter after the parenthesis.

Putting a space between the "(" and "l" does make capitalise the "L", but the layout doesn't look right with a space.

Even if the "(Location)" already uses a capital L, the code changes it to lower case.

Is there another code to use instead of vbProperCase?

Code used is:
Code:

If IsNumeric(Target.Value) = False Then _
Target = StrConv(Target.Text, vbProperCase)


Many thanks,
Jon