After sharing some of my favorite and most used shortcuts and functions/formulas in the last blog, it is time for some string functions.
The thing is, when talking about Excel, most people usually think about numbers, and string is defined as an array of characters.
But when you are analyzing the data, sometimes it is just not enough to crunch the numbers to get the needed results.
You have to manipulate the text as well. Or maybe you are just a visual type, trying to organize the spreadsheet you are staring at for most of your working days.
One way, or another, knowing string functions in Excel will come in pretty handy, and the reason Excel is the most widely used analytical tool in the world partly lies in its string functions. They are super powerful and easy, yet somehow underrated and underused.
1. TEXT IN PIVOT VALUE TABLES
I have to say this one was a huge find for me because I have used Pivots with just numbers in the values area for years. And it is probably the most unknown of all tips here.
Basically, with a little bit of effort, you can use number formatting to show text in the Pivot table. You can do it by adding measures in DAX (Data Analysis Expressions) formula language. You will probably need to follow it step by step for the first couple of times, though. :D
2. VALUE FORMULA
This formula is oriented more towards string transformation to numbers. Usually, Excel recognizes the data type, however, there are cases when the numbers are stored in a format that is unrecognizable to Excel. In those cases, the VALUE formula can come handy.
3. Extracting characters - LEFT, RIGHT, MID FUNCTIONS
If you want to extract some characters (or even whole words) from a string, you can use LEFT, RIGHT, or MID functions, depending on the positions of the characters.
4. String length - LEN FUNCTION
The LEN function is actually short for length. It gives you the number of characters in a string. I would use it when I needed to know if the account was synthetical or analytical. It includes the space as a character.
5. UPPER/LOWER (CASE)
I think this one is pretty self-explanatory. With the UPPER/LOWER function, you can change the case of a string.
6. Capitalizing first letters - PROPER FUNCTION
PROPER is a handy function if you want to capitalize the first letter in every word in a string, for example, when you have a bunch of names.
7. Remove the non-printable characters - CLEAN FUNCTION.
If you end up with special characters in your spreadsheet due to system export or copy-pasting from browsers or some other documents, you can use the CLEAN function to remove them.
The CLEAN function is applied by giving reference to a cell or entering the data directly into the argument by using double quotes.
8. Unnecessary space removal - TRIM FUNCTION
Sometimes you have an “invisible” space at the end of the string, which prevents you from using VLOOKUP/XLOOKUP, or you have extra space between the words.
One of my personal favorites that turned out to be a lifesaver on more than one occasion is a TRIM function. TRIM removes unnecessary spaces in the whole string.
9. Join text cells - CONCATENATE FUNCTION or & SYMBOL
If you have text in a couple of cells and want to combine them into a single cell, you can use CONCATENATE functions or just a simple & symbol. You do need to add spaces, otherwise, it will just merge the whole text together.
10. TEXT TO COLUMNS
On the other hand, if you have a lot of text in a single cell and want to split it, you can use TEXT TO COLUMNS.
This is one of the most commonly used features. You can find it in the Data ribbon.
You can either choose the delimiter from some common ones, like a comma:
You can add your own delimiter:
Or you can manually add the break line:
You can do so much more than just crunching the numbers in Excel. As you can see above, string functions have wide usage and can be extremely useful.
This is it for this issue of Excel tips and tricks. See you next time.
If you think that your financial models are overgrowing spreadsheets, and you need a better financial modeling tool, Farseer might be for you:
- Financial modeling in Farseer is centralized, fast, and consistent.
- You won’t need to worry about errors and mistakes.
- Natural language formulas are intuitive, error-proof, and cannot be deleted by accident.
- Sharing and exporting your work is granulated - you can share only parts of your model relevant to your user
- Built-in hierarchy makes modeling transparent by default
- Reorganize models by simply dragging and dropping entire spreadsheets to a specific location in the model.
LEARN MORE ABOUT FARSEER HERE