Thursday, 12 March 2015

How to Make Cell Contents or Formulas InVisible in Excel

Custom Cell Format to Make Cell Contents or Formulas InVisible in Excel
Generally in some cases if you dont want to show the Cell Contents/Formulas to the users ; we can apply  the < Custom Cell Format > to the Range or Selected cells as follows :

Custom Format Apply as below :
Select a Cell or Range that you want to make Invisible
Next Press 'Ctrl+1' to Go to Format Cells > Custom  
Next enter 3 Semicolons(;;;) in the < Type > box as shown below


Finally say 'OK'.

How to Get back from Invisible mode to Visible Mode ?

Simple , Change the Cells format to < General >

Note :
This method is very appropriate than making the Font Color to White.

Monday, 9 March 2015

How to Find the Count of Vowels and Consonants in a String in Excel VBA Macro

Excel VBA Macro to Find the Count of Vowels and Consonants in a String
Suppose we have a String " ExcEl kIngdOm fOr ExcEl vbA mAcrOs" as shown below. From this we can find the Count of Vowels and Consonants as follows :



Excel Formula to Count Consonants :

 =LEN(A1)-SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),{"a","e","i","o","u"},"" )))

Excel Formula to Count Vowels:

 =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),{"a","e","i","o","u"},"" )))

--------------------------------------------------------------------------------------------------
VBA Macro to Count Consonants :

Sub Consonent_Count()
Dim Cons_Count As Integer
Dim Str

Dim KCount As Integer
Dim i As Integer
Dim Chr As String

Str = ActiveSheet.Range("A1").Value
KCount = 0

For i = 1 To Len(Str)

Chr = UCase(Mid(Str, i, 1))

If Not Chr Like "[AEIOU]" Then
KCount = KCount + 1
End If

Next i

Cons_Count = KCount

MsgBox Cons_Count

End Sub
--------------------------------------------------------------------------------------------------
VBA Macro to Count Vowels:

Sub VowelsCount()
Dim Vowel_Count As Integer
Dim Str

Dim KCount As Integer
Dim i As Integer
Dim Chr As String

Str = ActiveSheet.Range("A1").Value
KCount = 0

For i = 1 To Len(Str)

Chr = UCase(Mid(Str, i, 1))

If Chr Like "[AEIOU]" Then
KCount = KCount + 1
End If

Next i

Vowel_Count = KCount

MsgBox Vowel_Count

End Sub

Note :
Hi My dear users you can use the above technique for your own requirement instead of Counting of Vowels and Consonants.

Thanks,
TAMATAM
        [ BI-Reporting Analyst ]

How to Find the Nth Largest or Nth Smallest Number in a Range in Excel

Excel LARGE and SMALL Functions to Find the Nth Largest or Nth Smallest Number in a Range
Suppose We have a Range of Numbers , from that data we can find the Nth Largest and Nth Smallest Numbers as below :

Nth Largest Number :
=LARGE(RANGE , N)

Example :
Suppose if you want to find the 5th Largest number from a Range of values , you can use the following formula :

=LARGE(A2:A20 , 5)


Nth Smallest Number :
=SMALL(RANGE , N)

Example :
Suppose if you want to find the 5th Smallest number from a Range of values , you can use the following formula :

=SMALL(A2:A20 , 5)

How to Find and Fetch the Maximum Occurred or Repetitive String or Number in a Range in Excel

Excel Function to Find and Fetch the Most Occurred or Repetitive String or Number in a Range
Suppose we have a data of Month Names as shown below :



In the above data 'Mar' is repeated more no.of times. We can fetch that using the below Combo Formula. 

{ =INDEX($A$2:$A$16,MATCH(MAX(COUNTIF($A$2:$A$16,$A$2:$A$16)),COUNTIF ($A$2:$A$16,$A$2:$A$16),0)) }

This is an array formula so that we have to use 'Ctl'+'Shift'+'Enter' key combination after enter the formula to get the result .



This will workout for numbers also.


Thanks,
TAMATAM

Thursday, 5 March 2015

How to Convert Numbers stored in Text Format to Number Format

Macro to Format or Convert the Data from the multiple columns to their respective Format
'To Convert the Number stored in a Text Format to Number Format
Method-I :
Sub Convert_Number2Text2Number()

For Each xCell In Selection

xCell.Value = CDec(xCell.Value)

Next xCell

'To Convert the Number Format as to show Negative Numbers in (Closed Brackets) with Red Color

Selection.NumberFormat = "0_);[Red](0)"

Eg :

-123 = (123)

'To Convert the Number to Text Format

'First convert the Data format to Text format Selection.NumberFormat = "@"

'Next convert each cell format to Text format

For Each xCell In Selection

xCell.Value = CStr(xCell.Value)


Next xCell

End Sub
--------------------------------------------------------------------------------------------------------------
Method-II :
'To Convert the Number stored in a Text Format to Number Format ;Dates stored in a Text format to Date Format ; Percentages stored in a Text format to Percentage.

The following Macro formats/converts the Data from the Multiple columns to their respective format by default.

Sub Format_Data()

Dim XlSht As Object

Set XlSht = Sheets("MySht")

For i = 1 To 13
            
Range(CStr(Choose(i, "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "Y", "AI")) & ":" & CStr (Choose(i, "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "Y", "AI"))).TextToColumns DataType:=xlDelimited
        
Next


End Sub

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts