Case converter or Macro for change case

Case converter or Macro for change case

Hi all Here in my office I have to change case of multiple ranges on daily bases, it`s very time consuming. So to solve this I create a macro , it`s very fast. You can also try this,

Just paste this code in your module and save.

Case converter or Macro for change case

Always save this file as Macro Enabled Excel Workbook

                                                                                                                                                    

'case converter By Vikash

Sub CaseConverterByVikash()

'case converter By Vikash

Dim ocell As Range, ans As String

ans = Application.InputBox("Type in Letter" & vbCr & _

    "(L)owercase, (U)ppercase, (S)entence, (T)itles ")

If ans = "" Then Exit Sub

For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)

    Select Case UCase(ans)

        Case "L": ocell = LCase(ocell.Text)

        Case "U": ocell = UCase(ocell.Text)

        Case "S": ocell = UCase(Left(ocell.Text, 1)) & _

            LCase(Right(ocell.Text, Len(ocell.Text) - 1))

        Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)

    End Select

Next

End Sub

.                                                                                                                                                           

Always open this file to perform this Macro

You can Also Download Exercise file from here Case converter or Macro for change case

Thanks in advance.

Read More

Find a special character with conditioins like (after any character or before any character)

Hi, i got a question from your side, the question is how can i find a special character with conditions like (after any character or before any character). I got some data also see the image below:-

 

Follow this simple steps.

STEP-1. we have to find something common(in array) and unique(in cell) here that value is "@" 

 

STEP-2. Find our required character, here that is "."(dot)

as per the image above, put the previous find function`s value as [start_num].

Step-3. finalizing the formula

Thanks in Advance.

Click here to download Exercise file. for further please contact.

Read More

How can I count a specific character in a single cell

Q . How can I count a specific character in a single cell ?

Ans.  In simple way  there  in no way to count a character in a single cell, but you can do it with different way. 

Follow this 

Step1. Calculate length of the Cell with   “=Len” formula. Shown below-

Step2. Substitute special character, with “=Substitute” Formula. See image below-

 

Step3. Find length of the cell after substitute.See image below-

Step4.Calculate Substitute of both lens (step2.-step3.). See image -

Step5. Merge the all formulas. see image-

 

 Summary- With this formulla we can count any charecter. step4's value is the count of the specific characters. If any doubts you can contact me. Contact Us
Click here to get Exercise file Count A Specific Character in a Single Column .Thanks In advance.

Read More