MS Excel Tips.
Advance MS Excel: Part-1. Commands - CONCATENATE, CHAR, TEXT, FIND, MID, MID, UPPER, REPLACE, SUBSTITUTE
1.
Join multiple fields in one field. Or adding
Prefix to a field. [See – Fig:2]
Syntax: = CONCATENATE(text1, text2, ... text_n) e.g. =CONCATENATE (A2, B2) or =CONCATENATE ("ABC",123)
2.
ASCII value to Character. [See – Fig: 1]
Syntax : = CHAR(ascii_value) CHAR(filed) e.g. =CHAR (65) or =CHAR (A2)
3.
Converting Numbers to a fixed length number with
a specific format. If you need to make all numbers to 7 digit number, follow
the following syntax. [see – Fig: 3 & Fig: 4]
Syntax: = TEXT( value, format ) e.g. TEXT(123,"0000000") or =TEXT(A2,"0000000")
4.
Getting the position of a character from a
text/string. For this “Find” function is used and it is case sensitive. [See – Fig:5]
Syntax: = FIND(find_text,within_text,start_num) e.g.
=FIND(" #",A2,1)
5.
Getting Substring from a String/text. For this “MID”
function is used. [See – Fig:6]
Syntaxt: =MID( text, start_position, number_of_characters ) e.g. =MID(A2, 1, 19) or =MID(A2,1,FIND(" #",A2,1)-1) or =MID("Happy", 1, 2)
Note: If you need only leftmost characters
or substring then you can simply use “LEFT” function. E.g. =LEFT(A2, 19). If you need only Rightmost
Characters or substring simply use “RIGHT” function. E.g. =RIGHT(A2,5)
6.
Convert letters/text to Lowercase
Syntax: = LOWER(text) e.g.=LOWER(A2)
7.
Convert letters/text to Uppercase.
Syntax: = UPPER(text) e.g.=UPPER(A2)
Note: Only Capitalized the first letter. Use
“PROPER” function. E.g. =PROPER(A2)
8. Replace a substring to a string/text or Updating
characters/substring in a text/string. [See- Fig:7]
Syntax: = REPLACE(old_text,start_num,num_chars,new_text) e.g. =REPLACE(A2,5,5,"Big Bang")
Note: If you want to replace a specific word then you can simply use “SUBSTITUTE”
function. It is casesensetive. [See-
Fig: 8]
Syntax: = SUBSTITUTE(text,old_text,new_text,instance_num) e.g. =SUBSTITUTE(A2, "music", "Big Bang")
0 comments for "Advance MS Excel: Part-1. Commands - CONCATENATE, CHAR, TEXT, FIND, MID, MID, UPPER, REPLACE, SUBSTITUTE"
Post a Comment