Creating a Dynamic Range for a Pivot Table Using the Offset

Creating a Dynamic Range for a Pivot Table Using the Offset

                               

REPT FUNCTION IN EXCEL

REPT()


REPT("this text",number of times)


=REPT(K,5) = KKKKK

=REPT(LOVE,3)= LOVELOVELOVE

UPPER FUNCTION IN EXCEL

UPPER(this text)

Example:-

=UPPER(kishan kumar)   = KISHAN KUMAR

=UPPER(Kishan Kumar)   = KISHAN KUMAR

LOWER FUNCTION IN EXCEL

LOWER(this text)

Example:-

=LOWER(Kishan Kumar)   = kishan kumar

=LOWER(KISHAN KUMAR)   = kishan kumar

PROPER FUNCTION IN EXCEL

PROPER(this text)

Example:-

=PROPER(kishan kumar)   = Kishan Kumar

=PROPER(KISHAN KUMAR)   = Kishan Kumar

EXTRACT TEXT FROM ALPHANUMERIC STRING IN EXCEL WITH VBA

PRESS ALT+F11
CLICK INSERT
MODULE
THEN PASTE THIS CODE


FOR TEXT SEPRATION

Function TextOnly(rng As Range) As String

Dim intChrCnt As Integer
    For intChrCnt = 1 To Len(rng)
        If IsNumeric((Mid$(rng, intChrCnt, 1))) = False Then
            TextOnly = TextOnly & Mid$(rng, intChrCnt, 1)
        End If
    Next
End Function

Extract number from alphanumeric string in excel

PRESS ALT+F11
CLICK INSERT
MODULE
THEN PASTE THIS CODE



Function GetNumber(s As String)
    Dim j As Long
    While Not IsNumeric(Left(s, 1))
        If Len(s) <= 1 Then
            Exit Function
        Else
            s = Mid(s, 2)
        End If
    Wend
    GetNumber = Val(s)
End Function

Use of CONCAENATE in Excel

	Name 1	Name 2	Concatenated Text	Formula
	Alan	Jones	AlanJones	=CONCATENATE(C3,D3)
	Bob	Williams	BobWilliams	=CONCATENATE(C4,D4)
	Carol	Davies	CarolDavies	=CONCATENATE(C5,D5)
	Alan	Jones	Alan Jones	=CONCATENATE(C6," ",D6)
	Bob	Williams	Williams, Bob	=CONCATENATE(D7,", ",C7)
	Carol	Davies	Davies, Carol	=CONCATENATE(D8,", ",C8)
What Does It Do?
This function joins separate pieces of text into one item.
Syntax
=CONCATENATE(Text1,Text2,Text3...Text30)
Up to thirty pieces of text can be joined.
Note
You can achieve the same result by using the & operator.
	Name 1	Name 2	Concatenated Text	Formula
	Alan	Jones	AlanJones	=C24&D24
	Bob	Williams	BobWilliams	=C25&D25
	Carol	Davies	CarolDavies	=C26&D26
	Alan	Jones	Alan Jones	=C27&" "&D27
	Bob	Williams	Williams, Bob	=D28&", "&C28
	Carol	Davies	Davies, Carol	=D29&", "&C29

CHAR() IN EXCEL

			ANSI Number	Character
			65	A				=CHAR(G4)
			66	B				=CHAR(G5)
			169	©				=CHAR(G6)

This function converts a normal number to the character it represent in the ANSI

character set used by Windows.

Syntax

=CHAR(Number)

The Number must be between 1 and 255.

AutoSum Shortcut Key

Auto Sum Short Key

Instead of using the AutoSum button from the toolbar,

you can press Alt and = to achieve the same result.

	Jan	Feb	Mar	Total
North	10	50	90	150
South	20	60	100	180
East	30	70	200	300
West	40	80	300	420
Total	100	260	690	1050

Formula  Alt+=  Syntax   =SUM(D12:D15)