Auto remove Unwanted capital letters.

thenvk

New Member
Joined
Mar 24, 2009
Messages
22
Hi,

Please help me with a way to remove the Unwanted capital letters in a sheet that contains text.

Also is there any way that I can define the removal (Ignore few words, Ignore words written in ' ', Ignore the first letter after '.' )

Thanks in advance.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Do you wish to simply remove all uppercase letters? Or do you wish to leave the letters/text but just convert to lowercase?

You might need to give us some before and after examples in order for us to help you get to where you want to be.
 
Upvote 0
try =PROPER(A1) assuming that your data starts in cell A1.

Also, you can try implementing the IF() condition in order to ignore certain characters / words.
 
Upvote 0
@Greg
Say if a cell have the following text.

This is A Test message. I Have been a member OF "Mr Excel" from past 2 days.its good to be a part of the Community.

I want the above sentense to be as follows.

This is a test message. I have been a member of "Mr Excel" from past 2 days.Its good to be a part of the community.

Here i made all the letters after '.' as capitals.
Not changed the words in "" ("Mr Excel" )
All the words started with small letter, other than the ones next to '.'


@stormseed, I tried =Proper(), which makes all the words start with Capital letter.

I am using office 2007 version.
 
Last edited:
Upvote 0
How about an UDF
=SentenseCase(A1)
Code:
Function SentenseCase(ByVal txt As String) As String
Dim e, x, i As Long, temp As String
For Each e In Split(txt,".")
    x = Split(Trim(e))
    x(0) = StrConv(Trim(x(0)), 3)
    If UBound(x) > 0 Then
        For i = 1 To UBound(x)
            If (Left(x(i),1) <> Chr(34)) * (Right(x(i), 1) <> Chr(34)) Then
                x(i) = LCase(x(i))
            End IF
        Next
    End If
    temp = temp & ". " & Join(x)
Next
SentenceCase = Mid(temp, 3) & "."
End Function
 
Upvote 0

Forum statistics

Threads
1,224,392
Messages
6,178,313
Members
452,840
Latest member
Kbean

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top