MS Excel is one of the most accessible and versatile tools used by professionals working in the finance sector. This versatile spreadsheet tool has a set of in-built functions to organise and automate data collection, organisation and extraction.
Vast amounts of data can be easily handled in MS Excel using various math, statistical, financial, informative, logical, and text functions. Read about the different types of text functions and learn how to use the text function in Excel to deal with your data.
What is Text Function in Excel?
More often than not, MS Excel is used to tabulate and organise number data. However, it can also deal with logical, error and text data. Microsoft Excel has a unique set of functions for each data type. A text function (an Excel string function) is a built-in formula for modifying, locating and extracting textual data in MS Excel.
How To Use Text Function in Excel
Text functions are crucial to increasing overall productivity and reducing time spent dealing with large amounts of text-based data in MS Excel. Here are the different types of text functions in Excel with examples.
LEFT Function
The Left function extracts the desired amount of characters from the left of a text. For example, if we want to extract four characters from the left of the text in cell B2, the syntax will be:
=LEFT(B2, 4)
RIGHT Function
Similar to the Left function, this function helps to extract characters from the rightmost side of the text. For example, to extract three characters from the right of the text in cell A2, the syntax is:
=RIGHT(A2, 3)
MID Function
MID is an MS Excel text function that extracts characters from the middle of a string. The syntax for extracting 7 characters from the middle of the text in B2, starting from the 5th character, is:
=MID(B2,5,7)
Spaces are included as characters in the MID function.
LEN Function
This Excel text function lists the length of characters in a given cell. The syntax for the example below is as follows:
=LEN(B2)
FIND Function
The FIND function displays the number of the first character of the phrase we are looking for in the text. For instance, if we want to find the position of the letter “tt” in the word Omelette, the syntax is as follows:
=FIND(“tt,B2)
The SEARCH function in MS Excel is similar to the FIND function, the only difference being that the former is not case-sensitive while the latter is.
PROPER Function
PROPER capitalises the first letter of each word in the selected cell. The syntax in the example given below is:
=PROPER(B2)
REPT Function
The REPT text function repeats a given text several times. For example, the word “Bunny Rabbit” can be written thrice using the formula:
=REPT(B2,3)
This syntax repeats the text without any spaces.
TRIM Function
TRIM removes any extra spaces before the text or between the words written in a cell. The syntax for the given example is:
=TRIM(B2)
UPPER Function
The UPPER text function converts all lowercase text in a cell to uppercase text in the selected cell. The syntax for the example given below is:
=UPPER(B2)
LOWER Function
This MS Excel text function converts text in uppercase to text in lowercase. It performs the opposite action of the UPPER function. The syntax for the example given is:
=LOWER(B2)
SUBSTITUTE Function
The SUBSTITUTE function can replace existing text in a string with a new one. Both strings have to be enclosed in double inverted commas (“[string]”). In the example below, let’s say we want to replace the word Dickens with Darwin. The formula for the substitution would be:
=SUBSTITUTE(B2,”Dickens”,”Darwin”)
This function is case-sensitive and will not be able to carry out the formula if the case of the old text does not match that of the given text.
CONCATENATE Function
This MS Excel text function merges the text from three or four cells. Suppose you want to join the text in cells B2, C2 and D2, as given in the example. You can use the syntax:
=CONCATENATE(B2,C2,D2)
This will combine the texts from these cells into one cell without any spaces.
You can add a space between the text in two cells by simply typing “ “ (a space enclosed by double inverted commas) between each cell value. To add spaces in the above example, the syntax is:
=CONCATENATE(B2,” “,C2,” “,D2)
Conclusion
The text functions in MS Excel are used to sort through large amounts of text and clean up and organise textual data. Some useful text functions in MS Excel include Left, Right, Upper Lower, Concatenate, Trim, Find and Substitute.
Are you looking to master MS Excel string functions and formulas? Check out the Microsoft Excel Course by The WallStreet School, India. Become an expert in MS Excel with ready-made practice templates created by industry experts and level up your finance career.
Get in touch with us via email or call +91-9953729651 for more information on this course.
FAQs
- What is the difference between formula and function in Excel?
In Excel, a formula is a calculation manually written by the user. Conversely, functions are already built into MS Excel. Users can select one or more functions to create an Excel formula string.
- How to use text formula in Excel?
Using the appropriate functions and Excel string formulas, you can use various text formulas in your spreadsheet. An Excel string always starts with an equal to (‘=’) sign, followed by the desired text function. Excel automatically shows the format in which you must input cell values, commas and other information as you write the string formula.
- What is the difference between CONCATENATE and CONCAT functions in MS Excel?
Both CONCATENATE and CONCAT can combine data scattered in different cells into one cell. However, there is one difference: CONCAT cannot ignore empty cells and places the cell’s value without the text in the resultant phrase. On the other hand, CONCATENATE can ignore empty cells and give the phrase without putting extra spaces or cell values.
- What is the TEXT function in Excel?
The TEXT function in Excel is used to format numbers, dates, or times as text in a specific format. For example, =TEXT(A1, “dd/mm/yyyy”) converts a date to a chosen format.
- How do I use the TEXT function in Excel?
To use the TEXT function in Excel, input =TEXT(value, format), where “value” is the cell you want to format, and “format” is the desired text format.
- What are some popular text functions in Excel?
Popular text functions in Excel include LEFT, RIGHT, MID, LEN, CONCATENATE, and TEXT, which are used to manipulate or format strings.
- What is an example of the TEXT function in Excel?
An example is =TEXT(1234.5, “$#,##0.00”), which converts the number 1234.5 into text, displaying “$1,234.50.”
- What are Excel string functions?
Excel string functions allow you to manipulate text strings, including functions like UPPER (to capitalize), LOWER (to lowercase), and LEN (to count characters).
- How do text functions in Excel help with data formatting?
Text functions in Excel, like TEXT and CONCATENATE, help format and combine data for better readability, especially for reports or presentations.
