ms excel text functions 3
DESCRIPTION
mxexelfunctionsTRANSCRIPT
TEXT FUNCTIONS
=CHAR(A1) Result: "v" =CHAR(A2) Result: "@" =CHAR(72) Result: "H" =CHAR(109) Result: "m"
CLEAN FUNCTION
CONCATENATE FXN
EXACT FUNCTIONS
FIND TEXT
Fixed functions
Left function
EXAMPLE
• =LEFT(A1, 5) Result: "Alpha“• =LEFT(A2, 8) Result: "techonth" • =LEFT("Excel", 2) Result: "Ex" • =LEFT("Excel") Result: "E" • =LEFT("Excel", 25) Result: "Excel"
Lower functions
Replace function
• EG=REPLACE(A1, 1, 5, "Beta") Result: "Betabet Soup" =REPLACE(A2, 5, 2, "1234") Result: "tech1234thenet.com" =REPLACE("apples", 2, 5, "te") Result: "ate"
Search function
Example
• =SEARCH("BET", A1, 3) Result: 6 • =SEARCH("e", A2) Result: 2 • =SEARCH("e", A2, 1) Result: 2 • =SEARCH("e", A2, 3) Result: 9 • =SEARCH("in", A2, 6) Result: #VALUE!
=SEARCH("cel", "Excel", 1) Result: 3
Substitute
Example
• =SUBSTITUTE(A1, "bet", "con", 1) Result: "Alphacon soup"
• =SUBSTITUTE(A2, "t", "4", 2) Result: "techon4henet.com"
• =SUBSTITUTE(A2, "t", "4") Result: "4echon4hene4.com“
Trim function
Example
• =TRIM(A1) Result: "Tech on the Net" =TRIM(A2) Result: "1234"
• =TRIM(A3) Result: "alphabet soup" • =TRIM(A4) Result: "www.techonthenet.com"
=TRIM(" apples ") Result: "apples"
Logical functions
Example
• =AND(A1>10, A1<40) Result: TRUE
• =AND(A1=30,A2="www.checkyourmath.com") Result: FALSE =AND(A1>=5, A1<=30,
• =AND(A1>=5, A1<=30,A2="www.techonthenet.com") Result: TRUE
IF Function
Example
• =IF(A1>10, "Larger", "Smaller") Result: "Larger"
• =IF(A1=20, "Equal", "Not Equal") Result: "Not Equal"
• =IF(A2="Tech on the Net", 12, 0) Result: 12
Or function
example
• =OR(A1<10, A1=40) Result: FALSE
• =OR(A1=30, A2="Microsoft") Result: TRUE
• =OR(A1>=5, A1=20, A2="techonthenet.com") Result: TRUE
Not function
Example
• =NOT(A2="techonthenet.com") Result: FALSE • =NOT(TRUE) Result: FALSE =NOT(FALSE) Result: TRUE
• =NOT(A1<10) Result: FALSE • =NOT(A2="Microsoft") Result: TRUE • =NOT(5+1=7) Result: TRUE
Bin2dec
Bin2hex
Examples
• =BIN2HEX(A1) Result: 3B • =BIN2HEX(111011) Result: 3B =BIN2HEX(A2) Result: 14
• =BIN2HEX(10100) Result: 14 • =BIN2HEX(111111) Result: 3F • =BIN2HEX(111111, 4) Result: 003F • =BIN2HEX(111111, 5) Result: 0003F
Statistical functions
Rank function-syntax
• The Microsoft Excel RANK function returns the rank of a number within a set of numbers.
• RANK( number, array, [order] )• Number- The number to find the rank for. • array -A range or array of numbers to use for
ranking purposes. • order -Optional. It specifies how to rank the
numbers.
Example
Example• =RANK(A4, A2:A6, 1) Result: 5 • =RANK(A4, A2:A6, 0) Result: 1 • =RANK(A3, A2:A6, 0) Result: 4• Note• If order is 0, the numbers are ranked in
descending order.• If order is not 0, the numbers are ranked in
ascending order.• If the order parameter is omitted, it assumes
order is 0 (descending order).
Large function
• The Microsoft Excel LARGE function returns the nth largest value from a set of values.
• Syntax-LARGE( array, nth_position )• array A range or array from which you want to
return the nth largest value. • nth_position The position from the largest to
return.
Example
Example• =LARGE(A1:A5, 1) Result: 32 • =LARGE(A1:A5, 2) Result: 8• =LARGE(A1:A5, 3) Result: 5.7 • =LARGE(A1:A5, 4) Result: 4 • =LARGE(A1:A5, 5) Result: -2.3• Note• If nth_position is larger than the number of values in array, the LARGE function will return the #NUM! error.
• If array is empty, the LARGE function will return the #NUM! error.
count• The Microsoft Excel COUNT function counts the number of cells that
contain numbers as well as the number of arguments that contain numbers.
• Syntax-COUNT( argument1, [argument2, ... argument_n] )
• argument1, argument2, ... argument_n Either ranges of cells or values. There can be up to 30 arguments.
Example
Example
• =COUNT(A1:A6) Result: 3 • =COUNT(A1:A6, 129) Result: 4
Countif
• The Microsoft Excel COUNTIF function counts the number of cells in a range, that meets a given criteria.
• Syntax-COUNTIF( range, criteria )• range The range of cells that you want to
count based on the criteria.• criteria determine which cells to count.
Example
Example
• =COUNTIF(A2:A7, D2) Result: 1 • =COUNTIF(A:A, D2) Result: 1 • =COUNTIF(A2:A7, ">=2001") Result: 4
Average function
• The Microsoft Excel AVERAGE function returns the average (arithmetic mean) of the numbers provided.
• Syntax-AVERAGE( number1, [number2], ... [number_n] )
• number1, number2, ... number_n -Numeric values that can be numbers, named ranges, arrays, or references to numbers. There can be up to 30 values entered.
Example
Example
• =AVERAGE(B2, B3) Result: 8.85 • =AVERAGE(B3, B5, 45) Result: 19.2
=AVERAGE(B2:B6) Result: 46.24
Sumif• The SUMIF function is a worksheet function that adds all numbers
in a range of cells based on one criteria (for example, is equal to 2000).
• SUMIF( range, criteria, [sum_range] )• range The range of cells that you want to apply
the criteria against.• criteria determine which cells to add.• sum_range Optional. It is the range of cells to
sum together. If this parameter is omitted, it uses range as the sum_range.
Example
Example• =SUMIF(A2:A6, D2, C2:C6) Result: 218.6 'Criteria
is the value in cell D2 • =SUMIF(A:A, D2, C:C) Result: 218.6 'Criteria
applies to all of column A (ie: A:A) • =SUMIF(A2:A6, 2003, C2:C6) Result: 7.2 'Criteria
is the number 2003 • =SUMIF(A2:A6, ">=2001", C2:C6) Result: 12.6
'Criteria is greater than or equal to 2001 =SUMIF(C2:C6, "<100") Result: 31.2 'Adds values in C2:C6 that are less than 100 (3rd parameter is omitted)
Countif and countifs Example-1
Example 2. Counting numbers with multiple criteria (AND logic)
• Syntax-=COUNTIFS(B2:B11,">0", D2:D11,">0")
Example-3-COUNTIFS for text values
=COUNTIFS(B2:B7,"=P",C2:C7,"=P",D2:D7,"=P",E2:E7,"=P")
Example-4
=COUNTIFS(B3:E3,"=P") =COUNTIF(B3:E3,"=P")
Date and Time function• The Microsoft Excel DATE function returns the serial
number of a date.• Syntax-DATE( year, month, day )• year -A number that is between one and four digits.• month -A number representing the month value. If the
month value is greater than 12, it adds that number of months to the first month of the year specified in the year parameter.
• day -A number representing the day value. If the day value is greater than the number of days in the month specified, it adds that number of days to the first day in the month specified in the month parameter.
Note
• Note• If the year is between 0 and 1899, the year
value is added to 1900 to determine the year.• If the year is between 1900 and 9999, the
DATE function uses the year value as the year.• If the year is greater than 9999, the DATE
function will return the #NUM! error.
Example
Example
• =DATE(A1,A2,A3) Result: 'Aug 31, 2012' =DATE(2012,8,31) Result: 'Aug 31, 2012' =DATE(2009,11,30) Result: 'Nov 30, 2009' =DATE(2009,11,31) Result: 'Dec 1, 2009' =DATE(2009,11,32) Result: 'Dec 2, 2009'
Day function
• DAY function returns the day of the month (a number from 1 to 31) given a date value.
• Syntax=DAY( date_value )• date_value A valid date to return the day of
the month.
Example
Example
• =DAY(A1) Result: 1 • =DAY(A2) Result: 31 • =DAY(A3) Result: 15 • =DAY("12/31/2011") Result: 31
Hour-function
• HOUR function returns the hour of a time value (from 0 to 23)
• Syntax-Hour( serial_number )• serial_number The time value to extract the
hour from. It may be expressed as a string value, a decimal number, or the result of a formula.
Example
Example
• =HOUR(A1) Result: 13 • =HOUR("4:30:00 AM") Result: 4
=HOUR("4:30:00 PM") Result: 16
Minute Function
• MINUTE function returns the minute of a time value (from 0 to 59).
• MINUTE( serial_number )• serial_number The time value to extract the
minute from. It may be expressed as a string value, a decimal number, or the result of a formula.
Example
Example
• =MINUTE(A1) Result: 14• =MINUTE("4:00:00 AM") Result: 0
=MINUTE("4:30:00 AM") Result: 30
Month
• MONTH function -returns the month (a number from 1 to 12) given a date value.
Example
• =MONTH(B2) Result: 9 • =MONTH(B3) Result: 8 • =MONTH(B4) Result: 4
=MONTH("12/31/2011") Result: 12
Now
• NOW function returns the current system date and time. The NOW function will refresh the date/time value whenever the worksheet recalculates.
• Syntax-NOW()• Parameters or Arguments• There are no parameters or arguments for the
NOW function.
Example
Today
• TODAY function returns the current system date. This function will refresh the date whenever the worksheet recalculates.
• Syntax-TODAY()