It is a daily reality in most companies using excel to have a messy spreadsheet due to manual entry or due to different import sources. As a result, we tend to make widespread adjustments to our data sets that will be time consuming and error-prone if done manually. We are so lucky that excel offers a range of functions that can help to make this job easier with more reliable result. And so, let’s talk more about how to use Text functions as easy as 1 2 3.
Before we discuss these functions let’s review the anatomy of a function first.
Let’s look at the MID function as an example: =MID(text, start_num, num_chars)
Equals – = Every function must start with an equal sign.
FUNCTION NAME – MID The standard syntax is to use upper-case letters, but the function will still work in lower-case letters, so don’t worry.
Open bracket – ( After a function name you must have an open bracket, “(“, after this open bracket you can start entering your arguments.
Arguments – text, start_num, num_chars These are the arguments that we input into a function. An argument is an input into a function, where a function reads this argument, and other arguments if needed, to process the function. You can directly type in arguments yourself or you can have other functions calculate the values of the arguments for you. We explain this later bit under nested functions below.
Cell reference – A2 Within a function, depending on the function, you may either need to specify a cell reference or you may need to specify a range. If a range is needed (not for this function), a range would be specified in the syntax: A2:A10. Here A2:A10 which reads in natural language as A2 to A10, in other words, the colon, “:” is the natural language equivalent of “to”.
Comma – , Commas are used to separate arguments within a function. This way, Excel knows that the input of one argument has ended and the input of the next argument is beginning. Some functions have only one argument, and thus, a comma will not be needed.
Close bracket – ) At the end of the function, you must have a close bracket, “)“, then Excel knows that you have stopped inputting arguments. In the latest versions of Excel, you can get away without typing the last bracket and then pressing Enter and Excel will automatically add this bracket for you.
Now that we already have reviewed the anatomy of a function let’s see how to use Text functions as easy as 1,2,3.
CONCAT
This function joins text together. It is the newer version of CONCATENATE function. The text being joined can be entered by referring to other cells, e.g. =CONCAT(C4, ” “,B4) or the text can be typed directly =CONCAT(“Demetria”,” “,”Abbey”), the comma specifies what you would like to join. Remember that when inputting text into a function use ” “ around the text.
CONCATENATE
This is the older version of CONCAT and is still available in the newer versions of Excel but you will not get the extra functionality.
CONCAT became available in January 2016 version of Excel, which is only available if you have the Office 365 version of Microsoft Office. You can read the release notes for the various versions of Excel and you can also find out which version of Excel you are using through this link.
LEFT
This function extracts a given number of characters from the left side of a supplied string. Say for example you wanted to get the first two numbers in this location 02-East 2635, your formula would be =LEFT(text, num_chars).
RIGHT
This function extracts a given number of characters from the right side of a supplied string. Say for example you wanted to get the first two numbers in this location 02-East 2635, your formula would be =RIGHT(text, num_chars).
MID
This function extracts a given number of characters from the middle of a supplied string. Say for example you wanted to get the first two numbers in this location 02-East 2635, your formula would be =MID(text, start_num, num_chars).
We can also have a function inside another function, where a function can be used as an argument. We call such functions as NESTED FUNCTIONS. This is widely used to some companies with so much details to compute.
Let’s take for example the MID function again.
=MID(A2,2,FIND(” “,A2))
Here, the FIND function is used within another function, MID, this entire then becomes a nested function. Excel will work the innermost function first and gradually work its way outwards. Inner functions are sometimes called helper functions.
Here, FIND(” “,A2) is used as the third argument of the MID function which should be num_chars, i.e. the number of characters. In other words, instead of us telling Excel the number of characters, and this value being static, the helper function, FIND, is telling Excel to find the number of characters, and hence this value becomes dynamic.
Having discussed that, we still have few functions to meet.
PROPER
This function converts the first character to upper case and rests to lower case. Basically, the PROPER function in excel is used to convert your input text to proper case. It can be used to capitalize each word in a given string. Say for example below.
The formula to make the Full Name was:
LOWER
This function converts all text to lower case. Say for example the full name will be used for the email in the company.
The formula to make the e-mail was:
You saw we used the “&” here however we can always use the CONCAT function if that’s the function you prefer to use.
TEXT JOIN
This is another function that can be used to join text together, this works well because of the following:
- You can specify once that you want a space between each word and don’t have to include a space each time like we did in CONCAT.
- You now have the choice to ignore empty cells in a range.
For example =TEXTJOIN(” “, FALSE, “JOHN”, “SMITH”) returns JOHN SMITH. The first argument specifies the separator you would like to see between each word (a space in this instance), the second argument specifies whether to ignore empty cells or not, and then the text follows. You can specify the text as a range, so =TEXTJOIN(” “, TRUE, A5:A12) is also valid and the text is contained in the specified range.
Like CONCAT, TEXTJOIN is only available in the latest Office 365 version of Excel.
There you have it, there are really so much to explore in Excel. I hope like me, you are free to explore these functions that can make our life easier may it be at work or on our everyday computation of expenses or even income.
The Text functions is useful in situations where you want to display numbers in a more readable format or you want to combine numbers with symbols or text. Do you want to watch these functions instead?
Then head on to my YouTube channel. See you there!
If you are looking for someone who knows their way around Data Entry & Spreadsheets (MS Excel, Word, Excel VBA), you can try my excel services.
