Principles of Financial Modelling

CHAPTER 24Text Functions and Functionality

INTRODUCTION

This chapter covers Excel's Text functions. These can be useful in many contexts, including facilitating the manipulation of numerical fields (such as by first turning them into text, operating on the resulting text fields and turning the new text back into a numerical field). The functions which we focus on are:

• CONCAT (and the legacy CONCATENATE) combine text from multiple ranges and/or strings. CONCAT also allows for a multiple-cell range to be concatenated into a single cell. TEXTJOIN has the additional functionality of allowing a specific delimiter to be used (equivalent to concatenation if no delimiter is defined), and to specify whether empty cells are to be ignored or not.
• VALUE converts a “numerical-looking” text field into its look-alike number. NUMBERVALUE converts text to number in a locale-independent manner, by allowing the specification of fields that define a decimal and a group separator. T returns a text argument as the same text, otherwise it returns an empty text cell.
• LEFT and RIGHT return the leftmost and rightmost characters from a text field, and MID returns a specific number of characters from the middle of a text field.
• LEN returns the number of characters in a text field.
• FIND finds one text value within another (case-sensitive). SEARCH is similar but not case-sensitive.
• REPLACE replaces all characters (between and specified starting and ending point) with new specified text. SUBSTITUTE replaces ...