Excel is all about working with numbers. Nevertheless, in case your information consists of an excessive amount of textual content, you don’t have to fret in any respect. Excel offers a number of functions that make it simpler to manipulate textual content strings. These functions allow you to simply discover a string, depend the characters in a string, take away additional areas from a string, be part of two or extra strings, and carry out different comparable duties on the textual information.
What are Text functions in Excel?
Text Functions are Microsoft Excel’s native functions that permit reworking or analyzing textual information. Excel offers a complete of 30+ Text functions and plenty of of those are sometimes utilized by folks for information evaluation. This submit highlights 10 such Text functions, with their makes use of and examples.
10 Text Functions in Excel with Examples
Following is the record of high 10 functions in Excel:
- FIND
- LEN
- LEFT
- RIGHT
- MID
- SUBSTITUTE
- UPPER
- TRIM
- CONCATENATE
- TEXT
Let’s take an in depth take a look at these functions, one after the other.
1] FIND
The FIND operate means that you can discover a textual content string inside one other. It returns the place at which a personality or string begins inside one other textual content string.
Syntax
FIND(find_text, within_text, [start_num])
- find_text argument is used to enter the textual content the person desires to look.
- within-text argument takes the textual content which accommodates the textual content that must be discovered.
- [start_num] is an optionally available argument that takes the place from the place to start out the search. It takes the worth 1 by default.
Instance
Allow us to say the A3 cell in an Excel sheet accommodates the string ‘The Windows Club’. If the person desires to seek out the place of ‘Win’ inside the string, he could use the ‘Find’ functions as:
f(x)=FIND("Win", A1)
The output of the above operate might be 5, as 5 represents the beginning place of the textual content ‘Win’ inside ‘The Windows Club’.
Word: The FIND operate is case-sensitive. If you don’t want to match the case, you need to use the SEARCH operate, which has the identical syntax because the FIND operate.
Learn: The best way to use the brand new TEXTSPLIT operate in Excel
2] LEN
The LEN operate calculates the size of the string, i.e. the variety of characters current in a string. It counts the areas as characters.
Syntax
LEN(textual content)
- textual content argument takes the string whose size the person desires to seek out.
Instance
Within the above instance, if the person desires to seek out the size of the string ‘The Windows Club’, he could use the ‘LEN’ operate as:
f(x)=LEN (A3)
The output of the above operate might be 16, as there are 16 characters in the string ‘The Windows Club’, together with areas.
Additionally learn: Arrows keys are usually not working in Microsoft Excel.
3] LEFT
The LEFT operate returns a number of successive characters from the left aspect of a string, primarily based on the quantity specified by the person.
Syntax
LEFT(textual content, [num_chars])
- textual content argument is used to specify the string that accommodates the characters that must be discovered.
- [num_chars] specifies the variety of characters to be extracted from the left of the primary string. This argument is optionally available. It takes ‘1’ as a default worth, if not specified by the person.
Instance
In the identical instance as said above, if the person desires to extract the primary 7 characters from ‘The Windows Club’, he could use the ‘LEFT’ operate as:
f(x)=LEFT (A3, 7)
The output of the above operate might be The Win, as these are the 7 leftmost characters in the string ‘The Windows Club’, together with areas.
4] RIGHT
The RIGHT operate is used to extract a number of characters from the intense proper of a string.
Syntax
RIGHT(textual content, [num_chars])
- textual content argument specifies the string that accommodates the specified characters.
- [num_chars] argument specifies the variety of characters that must be extracted, transferring from the intense proper to the left of the string. That is an optionally available argument that takes ‘1’ because the default worth if left unspecified.
Instance
Taking the identical instance, if the person desires to extract the final 7 characters from the string ‘The Windows Club’, he could use the ‘RIGHT’ operate as:
f(x)=RIGHT(A3, 7)
The output of the above operate might be ws Membership, since they’re the 7 rightmost characters in ‘The Windows Club’, together with areas.
5] MID
(*10*)
The MID operate returns a number of consecutive characters or a substring from the center of one other string.
Syntax
MID(textual content, start_num, num_chars)
- textual content argument takes the string that accommodates the specified characters.
- start_num argument takes the place from the place to start out extracting the characters.
- num_chars argument takes the variety of characters the person desires to extract from the string.
Instance
Within the above instance, if the person desires to extract 4 characters ranging from the third character in the string ‘The Windows Club’, he could use the ‘MID’ operate as:
f(x)=MID(A3, 3, 4)
The output of the above operate might be e Wi, as ‘e’ is the third character and staring from ‘e’ counting areas as properly, ‘e Wi’ are the 4 consecutive characters in the string ‘The Windows Club’.
6] SUBSTITUTE
The Substitute operate replaces an present textual content with a brand new textual content in a given string.
Syntax
SUBSTITUTE(textual content, old_text, new_text, [instance_num])
- textual content argument specifies the primary string.
- old_text argument specifies the textual content that must be changed.
- new_text argument specifies the textual content that must be put in place of the present textual content.
- [instance_num] argument specifies which occasion (or prevalence) of the present textual content is to get replaced. That is an optionally available argument. When you specify this worth, solely that occasion of the textual content might be changed; in any other case, all of the cases of the present textual content might be changed with the brand new textual content.
Instance
In the identical instance, if the person desires to substitute ‘Welcome to The’ for ‘The’ in ‘The Windows Club’, he could use the ‘SUBSTITUTE’ operate as:
f(x)=SUBSTITUTE(A3, "The", "Welcome to The")
The output of the above operate might be Welcome to The Home windows Membership, because the substitute operate has changed ‘The’ with ‘Welcome to The’ in the textual content string ‘The Windows Club’.
7] UPPER
The UPPER operate converts a string into uppercase, i.e., it returns a string after capitalizing every letter.
Syntax
UPPER(textual content)
- textual content argument takes the string that must be capitalized.
Instance
Following the identical instance, if the person desires to capitalize every letter in the string ‘The Windows Club’, he could use the ‘UPPER’ operate as:
f(x)=UPPER(A3)
The output of the above operate might be THE WINDOWS CLUB.
Word:
- If you wish to convert a string into lowercase, it’s possible you’ll use the LOWER operate, having the identical syntax as that of the UPPER operate.
- If you wish to capitalize the primary letter of every phrase in a string, it’s possible you’ll use the PROPER operate with the identical syntax.
8] TRIM
The TRIM operate removes all the additional areas inside a string, leaving simply 1 area between two phrases.
Syntax
TRIM(textual content)
- textual content argument takes the string with irregular spacing.
Instance
Within the instance said above, if the person desires to take away pointless areas from the string ‘The Windows Club’, he could use the ‘TRIM’ operate as:
f(x)=TRIM(A3)
The output of the above operate might be The Home windows Membership, leaving only a single area between phrases.
9] CONCATENATE
The CONCATENATE operate joins two or extra strings in Excel.
Syntax
CONCATENATE(text1, [text2], ...)
- text1 argument is necessary. It takes the primary string to affix.
- text2 argument takes the extra string to affix. You could be part of as much as 255 strings.
Instance
Allow us to say the A3 cell in an Excel sheet accommodates the string ‘The’, the A4 cell accommodates the string ‘Windows’, and the A5 cell accommodates the string ‘Club’. If the person desires to affix these strings, he could use the ‘CONCATENATE’ functions as:
f(x)=CONCATENATE(A3, " ", A4, " ", A5)
The output of the above operate might be The Home windows Membership, becoming a member of the strings in A3, A4, and A5 cells with areas between these strings.
Tip: Use the ampersand (&) image to concatenate two textual content strings.
10] TEXT
The TEXT operate converts the format of a quantity from ‘numeric’ to ‘text’. The operate can be utilized to position formatted numbers between textual content.
Syntax
TEXT(worth, format_text)
- worth argument takes the numerical worth that must be formatted.
- format_text argument takes the format that must be utilized to the worth.
Instance
Allow us to say the A2 cell in Excel accommodates the string ‘The Windows Club started on’ and the A3 cell accommodates the numeric information ’20-04-2009′; the 2 of those may be mixed in a single sentence utilizing the ‘CONCATENATE’ and the ‘TEXT’ functions as:
f(x)=A2&" "&TEXT(A3,"mmmm d, yyyy")&"."
The output of the above functions might be The Home windows Membership began on April 20, 2009.
Additionally learn: The best way to convert currencies in Excel.
What’s an instance of a textual content operate?
The TEXT operate in Excel is used to affix a formatted quantity with a textual content string. For instance, if an Excel sheet accommodates the string ‘Retails sales surge by’ in cell A1, and the quantity ‘20000’ in cell A2, then TEXT operate can be utilized to affix the content material of those two cells as:
f(x)=A1&" "&TEXT(A3,"$ ##,###")&".".
The above operate will return ‘Retails sales surge by $20,000.’, the place the quantity 20000 has been formatted utilizing a foreign money image and comma separator.
What’s the usage of decrease operate?
The LOWER operate is used to alter the case of a string to lowercase. If a given string is in uppercase, correct case, or sentence case, the LOWER operate will return the string with every of its alphabet transformed in small letters. The syntax for LOWER operate is LOWER(textual content), the place textual content specifies the string or reference to the cell that accommodates the string that must be transformed into lowercase.
Learn Subsequent: Prime 15 Monetary functions in Microsoft Excel.
