One of the popular uses of Excel is in data analysis and reporting applications. However, row data often must be organized and restructured before using in reports or other calculations. For example, when capturing individual names, product IDs, or other properties, row data captured through data collection tools can have different inputs for all information.
Most of the reorganizing can be done manually using Excel’s built-in features, but as you can imagine, automating it using relevant formulas has significant advantages. In this article, we’re going to be taking a look at the text formulas that can shorten data organization task.
Look & Feel
These functions can help change the looks or formatting of your data. Using these functions effectively will improve data readability and clarity.
TEXT
This function can apply formatting to a numeric value. You can think this function as an alternative to the number formatting feature that can be accessed through the corresponding menu in Excel. Although the TEXT function converts numerical values to text and prevents using them in calculations, it can help in certain cases. For example, if you need to format a value that is in the middle of a sentence, this formula is probably your best option.
Syntax
TEXT(number, format)
Example
| =”This is “&TEXT(0.0469,”0.0%”)&” the new way” | This is 4.7% the new way |
| ="My birthday is on "&TEXT(43239,"dddd, mmmm dd") | My birthday is on Saturday, May 19 |
PROPER, LOWER, UPPER
When you extract data from an external source, such as a form, there is a good chance that the data will come in a mix of upper and lower case characters. You can streamline such data entry applications using the PROPER, LOWER, UPPER functions LOWER and UPPER functions convert all characters in a string into all lowercase, and all uppercase characters respectively. The PROPER on the other hand, will capitalize only the first letter of the each word, while converting the rest of them to lowercase characters. The PROPER function works great with name, title, or other similar inputs.
Syntax
PROPER(text)
LOWER(text)
UPPER(text)
Example
| =PROPER("jOhn DoE is Name.") | John Doe Is Name. |
| =LOWER("jOhn DoE is Name.") | john doe is name. |
| =UPPER("jOhn DoE is Name.") | JOHN DOE IS NAME. |
TRIM, CLEAN
A potential issue with data collection applications is unnecessary space characters, or other special characters that cannot be printed such as new line or return characters. You can use the TRIM to eliminate spaces and the CLEAN to remove non-printable characters.
Syntax
=TRIM(text)
=CLEAN(text)
Example
| =CLEAN(" John Doe") |
JohnDoe |
| =TRIM(" John Doe ") | John Doe |
Info
The functions in this section are for providing workbook insight, such as finding text length in a cell or searching for data. Although you can always use these formulas as is, they work wonders when used in combination with parsing formulas.
LEN
This function returns the text length of the selected cell, in other words the character count. This can come in handy when checking input character length for validation, or can be used with FORMULATEXT function to measure formula length (cannot be more than 255). Another common and useful combination is with MID and RIGHT functions, with which parsing length can be calculated.
Syntax
=LEN(text)
Example
| =LEN("By the Power of Grayskull") | 25 |
| =LEN("I have the power!") | 17 |
| =RIGHT("He-man",LEN("He-man")-FIND("-","He-man")) | man |
FIND, SEARCH
Both of these functions essentially do the same thing, search a specified text inside a source value and return the index number of the first character of the searched text. While the FIND function does a case-sensitive search, SEARCH does not. They also have a 3rd argument than search text and source text, which indicates where the search process begins. If it is set to ‘5’, the functions start at the 5th character.
Syntax
=FIND(find text, within text, starting position)
=SEARCH(find text, within text, starting position)
Example
| =FIND("of","Keeper of the secrets of Castle Greyskull") | 8 |
| =SEARCH("of","Keeper of the secrets of Castle Greyskull") | 8 |
| =FIND("of","Keeper of the secrets of Castle Greyskull",10) | 23 |
| =SEARCH("of","Keeper of the secrets of Castle Greyskull",10) | 23 |
| =FIND("castle","Keeper of the secrets of Castle Greyskull") | #VALUE! |
| =SEARCH("castle","Keeper of the secrets of Castle Greyskull") | 26 |
Parsing
LEFT, MID and RIGHT functions can crop the specific part of the text inside cells and are fairly easy to use. The formula parameters determine how many characters are to be parsed. To make parsing dynamic, the search parameters can be provided by LEN, SEARCH and FIND functions.
LEFT
The LEFT function, as the name suggests, returns any number of characters from the target cell, starting from the left. If you are not sure about how many characters you want to return, use FIND or SEARCH functions to find a common separator index, and use 1 less of that.
Syntax
LEFT(text, character count)
Example
| =LEFT("He-man",2) | He |
| =LEFT("She-ra",3) | She |
| =LEFT("He-man",FIND("-","He-man")-1) | He |
| =LEFT("She-ra",FIND("-","She-ra")-1) | She |
MID
Using the MID function, you can define a start point and data will be pulled after that point. Other 2 arguments of this formula define the text reference and how many character will be returned.
Syntax
MID(text, start index, character count)
Example
| =MID("Man-at-arms",5,2) | at |
| =MID("Man-at-arms",FIND("-","Man-at-arms")+1,2) | at |
| =MID("Man-at-arms",
FIND("-","Man-at-arms")+1, FIND("-","Man-at-arms", FIND("-","Man-at-arms")+1)- (FIND("-","Man-at-arms")+1) ) |
at |
Note that the last example uses FIND functions inside another FIND function to pinpoint the search area for the "-" character after first one.
RIGHT
This function returns a text string starting from the last character of your selection. You can think of this as a reverse LEFT function. To make this function pull references dynamically you can combine it with other functions like LEN, FIND, or SEARCH.
Syntax
RIGHT(text, character count)
Example
| =RIGHT("He-man",3) | man |
| =RIGHT("She-ra",2) | ra |
| =RIGHT("He-man",LEN("He-man")-FIND("-","He-man")) | man |
| =RIGHT("She-ra",LEN("She-ra")-FIND("-","She-ra")) | ra |
Update
For when you need to update only a part of a text data, the two functions we’re going to cover in this group will come in handy.
SUBSTITUTE
The SUBSTITUTE function replaces a specified text with a new one, a certain number of times. The replacement starts from the first character of the main text. Subsequently, if a number of times parameter is entered, the replacement will repeat as many times as specified, and will stop.
Syntax
SUBSTITUTE(text, old text, new text, [number of times])
Example
| =SUBSTITUTE("By the power of Greyskull! I have the power!","power","force") | By the force of Greyskull! I have the force! |
| =SUBSTITUTE("By the power of Greyskull! I have the power!","power","force",1) | By the force of Greyskull! I have the power! |
REPLACE
The REPLACE function replaces a certain number of characters, starting from the specified index. This formula is more suitable for data fields of constant length and positioning.
Syntax
REPLACE(text, start index, character count, new text)
Example
| =REPLACE(H11,10,4,"XXXX") | A8AD2FCE-XXXX-5D99-D25D-A9239DAB8060 |
| =REPLACE(H12,10,4,"XXXX") | CCA86771-XXXX-A5B9-162E-556611077D6D |
| =REPLACE(H13,10,4,"XXXX") | 006C2F92-XXXX-2442-B8A6-98F0A34FCABA |
| =REPLACE(H14,10,4,"XXXX") | 0E588DDE-XXXX-6D03-A93F-C8EDA4DAFF44 |
Generate
The formulas under this category can join and create text strings. Need to combine first and last name columns? Keep reading below!
CHAR
The CHAR function returns the numerical codes of corresponding characters. This is a very useful feature for when you need to add nonprintable characters into a cell, such as a new line character. Such characters can also be removed using the CLEAN function.
The numerical codes can be different based on your operating system. For example, while CHAR(10) os ‘add a new line’ in Windows systems, you need to use CHAR(13) if you’re using a Mac. For more details,
Detailed example: https://www.spreadsheetweb.com/random-password-excel/
Windows: http://en.wikipedia.org/wiki/Windows-1252
Mac: http://en.wikipedia.org/wiki/Mac_OS_Roman
Syntax
CHAR(character code)
Example
| =CHAR(55) | 7 |
| =CHAR(36) | $ |
| =CHAR(65) | A |
| =CHAR(122) | z |
CONCATENATE
The CONCATENATE formula can merge multiple strings that can be up to 255 arguments. It works pretty much the same way the ampersand (&) operator works in formulas. For more information on the CONCATENATE function, see our related article.
Syntax
=CONCATENATE(text1, [text2], …)
Example
| =CONCATENATE("By the power of Greyskull!","I have the power!") | By the power of Greyskull!I have the power! |
| =CONCATENATE("By the power of Greyskull!"," ","I have the power!") | By the power of Greyskull! I have the power! |
| =CONCATENATE("By the power of Greyskull!",CHAR(10),"I have the power!") | By the power of Greyskull! I have the power! |
CONCAT
The CONCAT function is the updated version of CONCATENATE, which essentially works the same way. The biggest drawback of the CONCATENATE is that you need to select each target cell one by one. The CONCAT formula overcomes this issue as it can work with ranges.
Syntax
CONCAT(text1, [text2],…)
Example
| =CONCAT(A1:B3) | By the power of Greyskull! I have the power! |
| =CONCAT(A1:B3) | By the power of Greyskull! I have the power! |
TEXTJOIN
The TEXTJOIN is a relatively new addition to Excel’s plethora of functions. Some would argue that it’s more versatile that both concatenating functions. While both concatenating functions require entering a separator after each selection, TEXTJOIN doesn’t need this. You can also choose to skip or include blank cells.
Syntax
TEXTJOIN(delimiter, should ignore empty?, text1, [text2], …)
Example
| =TEXTJOIN(" ",TRUE,"This","is","Cringer…","my","fearless","friend.") | This is Cringer... my fearless friend. |
