Auto remove Unwanted capital letters.

[thenvk]

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.

 

[Greg Truby]

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.

 

[Stormseed]

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.

 

[thenvk]

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

 

[Legacy 68668]

How about an UDF
=SentenseCase(A1)

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