Hello Everyone, Today we'll discuss How to use Text Functions in Excel. Text Functions return an information about a text string and also used to combine a text string and a number together. Excel string Functions have been grouped into various category and today we'll discuss some Text Function Category like Text functions to remove an extra character, Functions to convert excel data type, Function to convert the Text in LOWER case and UPPER case.
Here we have some non-printable character in column A and our aim is to remove this non-printable character for that we use the CLEAN function.
How to Use CLEAN Function in Excel
The CLEAN text function is used to remove all non-printable characters from the text.
Formula used: =CLEAN(text)
Use of TRIM Function in Excel
Trim Text Functions is used to remove extra spaces (except single spaces) between words from text strings.
Formula Used: =TRIM(text)
Here we have a text string "Excel sheet 1" with some extra spaces so here we used TRIM function to removes the extra spaces.
How to Create LEFT Function in Excel
If you want to return a certain number of characters from the text string then you can use LEFT functions. Basically, LEFT function returns a specified number of characters from the beginning of the text strings.
Formula used: =LEFT(text,[num_chars])
Note: [num_chars] is set to the default value 1 means return first character of the strings.
Here you have a text string "EXCEL TEXT" and you want to first 5 characters of the string then you can use LEFT function to fetch the character from the begging of the text strings.
How to Create RIGHT Function in Excel
If you want to return a certain number of characters from the text string then you can use RIGHT functions and the RIGHT function returns a specified number of characters from the end of the text strings.
Formula used: =RIGHT(text,[num_chars])
Note: [num_chars] is set to the default value 1 means return first character from the end of the text strings.
Here you have a text string "EXCEL TEXT" and you want to 7 characters from the end of the text string then you can use RIGHT function to fetch the character from the end of the text strings.
How to Create MID Function in Excel
If you want to return a certain number of characters from the text string then you can also use Excel MID functions and MID function returns a specified number of characters from the MID of the text strings.
Formula used: =MID(text, start_num, [num_chars])
Note: start_num is the integer value that specifies the position of the first character and [num_chars] cannot be blank.
Here you have a text string "EXCEL TEXT" and you want to 4 characters from the seventh position of the beginning of the text string then you can use MID function to fetch the character from the specified position of the text strings.
Use of CONCATENATE Function in Excel
CONCATENATE Text Functions is used to combine two or more string together into one combined text strings.
Formula Used: =CONCATENATE(text1," ", text2.........)
NOTE: " " is used for single space between two text string or word.
Here we have a two or more text string in a different location and we want to combine all the strings in a single location then use the CONCATENATE text function like combine AMIT and KUMAR and get AMIT KUMAR in a single cell.
Use of REPT Function in Excel
REPT text function returns specified number of times repeated text string.
Formula used: REPT(text, number_times)
Note: If number_times is zero then it returns an empty string.
This function is used to get the multiple times of text string like we have a text string "ha" in cell A2 and we use the REPT function to get the repeated text strings and see the results in cell C2.
Create LEN Function in Excel
LEN function returns the length or number of character of the text string.
Formula Used: LEN(text)
Note: Empty string has length zero and a single space also count in the length.
This function is used to count the length of the text string like in cell A5 we have a text string "SUNIL KUMAR JAIN" and using the LEN function we find a length of this text string.
Create FIND Function in Excel
Excel Find function in excel returns the position of a specified character or sub-strings within the text strings.
Fomula Used: =FIND(find_text, within_text, [start_num])
Note: Find Function is a Case-sensitive function.
This function basically return the accurate position of the desired character like we want to find "m" character in cell A5 and we can fix the starting position of the text string so this function returns the position of the "m" character.
Use SEARCH Function in Excel
The Search functions in excel return the position of a specified character or sub-strings within the text strings. This function is similar to the FIND function.
Formula Used: =SEARCH(find_text, within_text, [start_num])
Note: A SEARCH function is not CASE-SENSITIVE function.
This function works same as the FIND function but a little difference is that it is not a CASE- SENSITIVE function means if we want to search "M" character in the text string but we have the "m" character in the text string then SEARCH function returns the position of the "m" character.
Use EXACT Function in Excel
The EXACT function returns TRUE as a result if two strings or value are exactly equal otherwise, returns FALSE if two strings or value do not match.
Formula used: =EXACT(text1, text2)
Note: Exact Function is a Case-Sensitive
The EXACT function is used to compare two strings means if the characters of the two different string are same then it returns TRUE as a result otherwise return FALSE as a result and the main point is that it is a CASE-SENSITIVE function.
Use T Function in Excel
The T function is used to check the supplied value is TEXT or not. If supplied value is TEXT then return the text otherwise, return the empty text strings.
Formula used: =T(Supplied value)
The T function is a very simple function, it check the text string either the string is text or not. If the input string is text like "AMIT" then T function return "AMIT" as a result while if the input string is any other format like value "0.5" then T function returns an empty string.
Create REPLACE Function in Excel
The REPLACE function is used to replace the text string with another string. You can also replace the part of the text strings with another part of the strings.
Formula used: =REPLACE(old_text, start_num, num-chars, new_text)
Note: The old_text is the text string that you want to replace and start_num is the position of the first character within old_text and num_chars is the number of character to replace and new_text is replacement string or part of the string.
This function is used to replace the text string or part of the text with another text. Here we are 4 parameters to use this function. First is old text like "KUMAR" , second is start_num means the position of the first character of "KUMAR", third is num_chars means number of character in text "KUMAR", and finally fourth is new_text like "SINGH".
Create SUBSTITUTE Function in Excel
The SUBSTITUTE function replaces one or more instances of a text string within an original text string. This function is similar to the REPLACE function.
Formula used: =SUBSTITUTE(text, old_text, new_text, instance_num)
Note: This function is a CASE- SENSITIVE.
The working of this function is same as the REPLACE function but the difference is that it replaces the instances of the text string and also the CASE-SENSITIVE function. In cell A4 the instance "an" is replaced by "A".
How to Create UPPER Function in Excel
The UPPER function is used to convert all the characters of the supplied string in the UPPER case.
Formula used: =UPPER(text)
How to Create LOWER Function in excel
The LOWER function is used to convert all the characters of the input string in the LOWER case.
Formula used: =LOWER(text)
This function convert all characters in LOWER case like we have a text string "excEL" and we apply the LOWER function then this function changes all the character in the LOWER case.
How to Create PROPER Function in Excel
The PROPER function is used to convert all the characters of the supplied string in the proper case means to arrange the string in the UPPER case and LOWER case.
Formula used: =PROPER(text)
NOTE: The first The first character of the every word is converted in the UPPER case by the PROPER function.
Use CHAR Function in Excel
The CHAR function returns the character according to an input character set number from 1 to 255 and outcomes get from the ANSI character set.
Formula used: =CHAR(number)
Note: (1) The character set may vary in the different computer system so the result can also vary in the different computer.
(2) You can also get #VALUE! error from the CHAR function. It occurs when the input number is outside of the range 1 to 255.
This function is used to get the character corresponding to the number according to the ANSI character set like we enter the number 63 in cell A2 and apply the CHAR function so get the character corresponding to the number "?".
Use CODE Function in Excel
The CODE function converts the first character of the input text string into the associated numeric set code. The outcomes of the CODE Function get from the ANSI character set.
Formula used: =CODE(text)
NOTE: Return code may vary across the different operating system.
This function is opposite to the CHAR function means returns the numeric code corresponding to the first character of the text string like we have a text string "Excel" then by using the CODE function we get the numeric code of the first character "E".
Create DOLLAR Function in Excel
The DOLLAR function converts an input number to a specified number of decimal places and we can also change the currency format by the DOLLAR function.
Formula used: =DOLLOR(number,[decimals])
Create FIXED Function in Excel
The FIXED Function converts a supplied number to a specified number of decimal places and then convert into a text.
Formula used: =FIXED(number,[decimals], [no_commas])
Note: (1)Number indicate the input number.
(2)[decimals] specifies the number of decimal places that display after the decimal point.
(3) [no_commas] specifies the return text should separate thousands by the comma. It has two value either TRUE or FALSE. TRUE means commas are not included in the resulting text while FALSE means commas are included in the resulting string.
Create TEXT Function in Excel
The TEXT function is used to convert a numeric value to text according to the user defined format.
Formula used: =TEXT(value, Format_text)
Create BAHTTEXT Function in Excel
The BAHTTEXT function is used to convert a number into Thai text and adds a suffix "Baht".
Formula used: =BAHTTEXT(number)
Use VALUE Function in Excel
The VALUE function is to convert a supplied text string into a numeric value.
Formula used: =VALUE(text)
Conclusion:
Thus, in this post, you will find How to Use Excel Text Functions. As we all know Text function is very helpful to convert any text string into the particular format as we want. If you like this post then you can share with friends, colleagues, and relatives. We'll update this information on the regular basis. You can also share this post on facebook, twitter, Google+ or other social media website. We hope that you like this post. If you have any query regarding this post you can freely write in the comment section. We'll revert back to you.
No comments:
Post a Comment