[ad_1]
What are text functions in Excel?
Text functions are native Microsoft Excel functions that allow you to transform or analyze text data. Excel provides over 30 text functions in total, and many of them are often used by people to analyze data. This post talks about 10 such text functions with their usage and examples.
10 Text Functions in Excel with Examples
Below is a list of top 10 features in Excel:
- FIND
- LINEN
- LEFT
- RIGHT
- AVERAGE
- REPLACE
- UPPER
- UNDERCUT
- CONNECT
- TEXT
Let’s take a look at these features in detail, one by one.
1]FIND
FIND The function allows you to find a text string inside another. It returns the position at which a character or line begins in another text string.
SyntA x
FIND (find_text, within_text, [start_num])
- find_text used to enter the text the user wants to search for.
- within-text takes the text that contains the text to be found.
- [start_num] an optional argument that takes the position where the search starts. The default value is 1.
Example
Let’s say cell A3 on an Excel sheet contains the string “Windows Club”. If the user wants to find the “Win” position in a string, he can use the “Find” functions as follows:
f(x)= FIND ("Win", A1)
The output of the above function will be 5, since 5 represents the starting position of the “Win” text in the “Windows Club”.
Note: The FIND function is case sensitive. If you don’t want to match case you can use SEARCHwhich has the same syntax as the FIND function.
2]LEN
function LEN calculates the length of the string, i.e. the number of characters present in the string. It counts spaces as characters.
Syntax
LEN(text)
- text takes the string the user wants to find the length of.
Example
In the example above, if the user wants to find the length of the “Windows Club” string, he can use the “DLSTR” function like this:
f(x)=LEN (A3)
The output of the above function will be 16 as there are 16 characters in the string “Windows Club” including spaces.
Arrow keys do not work in Microsoft Excel.
REMAINING 3
LEFT The function returns several consecutive characters from the left side of the string, depending on the number specified by the user.
Syntax
LEFT (text, [num_chars])
- text is used to specify a string containing the characters to be found.
- [num_chars] specifies the number of characters to be extracted to the left of the main string. This argument is optional. The default value is “1” if not specified by the user.
Example
In the same example as above, if the user wants to extract the first 7 characters from “Windows Club”, he can use the “LEFT” function like this:
f(x)= LEFT (A3, 7)
The output of the above function will be The Winsince that’s the 7 leftmost characters in the “Windows Club” string, including spaces.
4]RIGHT
RIGHT The function is used to extract multiple characters from the rightmost end of a string.
Syntax
RIGHT (text, [num_chars])
- text specifies a string containing the desired characters.
- [num_chars] specifies the number of characters to extract, moving from the far right of the string to the left. This is an optional argument that takes “1” as the default value if not specified.
Example
Let’s take the same example, if the user wants to extract the last 7 characters from the string “Windows Club”, he can use the “RIGHT” function as follows:
f(x)= RIGHT (A3, 7)
The output of the above function will be ws Club as it is the 7 rightmost characters in “Windows Club” including spaces.
5]MEDIUM
function MID returns multiple consecutive characters or a substring from the middle of another string.
Syntax
MID(text, start_num, num_chars)
- text takes a string containing the desired characters.
- start_num occupies the position from which character extraction begins.
- num_chars takes the number of characters the user wants to extract from the string.
Example
In the example above, if the user wants to extract 4 characters starting from the 3rd character in the “Windows Club” string, he can use the “MID” function like this:
f(x)=MID(A3, 3, 4)
The result of the above function will be e Wi, since “e” is the third character, and also, starting with “e” in counting spaces, “e Wi” is 4 consecutive characters in the “Windows Club” string.
6]REPLACE
Substitute the function replaces existing text with new text in the given string.
Syntax
SUBSTITUTE (text, old_text, new_text, [instance_num])
- text defines the main string.
- old_text specifies the text to be replaced.
- new_text specifies the text to replace the existing text.
- [instance_num] specifies which instance (or occurrence) of existing text is to be replaced. This is an optional argument. If you specify this value, only that instance of the text will be replaced; otherwise, all instances of the existing text will be replaced with the new text.
Example
In the same example, if the user wants to replace “Welcome to The” with “The” in the “Windows Club”, they can use the “REPLACE” function as follows:
f(x)= SUBSTITUTE (A3, "The", "Welcome to The")
The output of the above function will be Welcome to Windows789since the replacement function replaced ‘The’ With ‘ Welcome to The‘in text string’ Windows789‘.
7]UPPER
UPPER The function converts the string to upper case, i.e. returns the string after the capital letter.
Syntax
UPPER (text)
- text takes a string, which must be capitalized.
Example
Following the same example, if the user wants to capitalize every letter in the “Windows Club” string, they can use the “CAPITAL” function as follows:
f(x)= UPPER (A3)
The output of the above function will be THE WINDOWS CLUB.
Note:
- If you want to convert a string to lower case you can use LOWERwhich has the same syntax as the UPPER function.
- If you want to capitalize the first letter of every word in a string, you can use PROPER the same syntax.
8]FINISH
function TRIM removes all extra spaces in a string, leaving only 1 space between two words.
Syntax
TRIM(text)
- text takes a non-uniformly spaced string.
Example
In the example above, if the user wants to remove unnecessary spaces from the string “Windows789”, he can use the “TRIM” function as follows:
f(x)=TRIM(A3)
The output of the above function will be Windows789leaving only one space between words.
9]CONNECT
function CONCATENATE concatenates two or more rows in Excel.
Syntax
CONCATENATE(text1, [text2], ...)
- text1 is required. The connection requires the first line.
- text2 accepts an extra string to connect to. You can concatenate up to 255 lines.
Example
Let’s say cell A3 on an Excel worksheet contains the string “The”, cell A4 contains the string “Windows”, and cell A5 contains the string “Club”. If the user wants to concatenate these strings, he can use the “CONCATENATE” functions like:
f(x)=CONCATENATE(A3, " ", A4, " ", A5)
The output of the above function will be Windows789connecting the lines in cells A3, A4 and A5 with spaces between these lines.
Tip: use an ampersand (&) character to concatenate two text strings.
10]TEXT
TEXT The function converts the number format from “number” to “text”. This function can be used to place formatted numbers between text.
Syntax
value TEXT (, format_text)
- value takes the numeric value to be formatted.
- format_text accepts the format to be applied to the value.
Example
Let’s say cell A2 in Excel contains the string “The Windows Club has started” and cell A3 contains the numeric data “20-04-2009”; two of them can be combined into one sentence using the “CONCATENATE” and “TEXT” functions as follows:
f(x)=A2&" "& TEXT (A3,"mmmm d, yyyy")&"."
The result of the above functions will be the Windows Club launched on April 20, 2009.
How to convert currencies in Excel.
What is an example of a text function?
The TEXT function in Excel is used to concatenate a formatted number with a text string. For example, if an Excel worksheet contains the string “Retail sales increased by” in cell A1 and the number “20000” in cell A2, then you can use the TEXT function to combine the contents of the two cells as follows:
f(x)=A1&" "& TEXT (A3,"$ ##,###")&".".
The above function will return “Retail sales increased by $20,000” where the number 20,000 has been formatted with a currency symbol and a comma separator.
What is the use of the bottom function?
The LOWER function is used to change the case of a string to lowercase. If the given string is in uppercase, propercase, or sentence case, the BELOW function will return a string with each of its alphabets converted to lowercase. The syntax for the LOWER function is: LOWER(text), where text specifies a string or cell reference containing the string to be converted to lower case.
In this article, we have covered 10 text functions in Excel with examples. If you have any questions please don’t hesitate to ask useguide.net in the comments section below.
[ad_2]
Source link
windows789.com