Text

Chapter 11, Using Formulas to Manipulate Text

• Text can be up to 32,000 characters long, who knew?
• If you click on formulas Function Library Text you see there is quite a list of text functions.
• We need to review some of these.
• Concatenation
  • Joining two strings
  • We have seen that & is concatenation.
    • ="hello" & " world"
    • Place text in two cells and join with &
  • Textjoin is a new function
    • =textjoin(delim, skip_empty, range)
    • The delimiter is a string to place between items.
      • This can be any string, including the empty string.
    • The skip_empty tells excel to skip empty cells
    • The range is the text to join.
    • Put a, b and c in cells with a blank between them.
    • =textjoin("?",true,range)
    • =textjoin("?",false,range)
    • =textjoin("",false,range)
    • =textjoin(" AND ",false,range)
    • Combine columns L, M and N with the work " in " in column O
    • =concat(s1,s2, ... ) does this as well.
• Left, Right, Mid
  • =left(text, optional number of characters)
  • By default the number of characters is 1.
  • There does not appear to be an upper limit, stops at length of string.
  • Right works this way too.
  • =mid(text, start, length)
• Case switchers
  • upper(), lower() change all text to upper or lower
  • Proper makes the first letter of each word upper case.
  • he gives this example =upper(left(c4,1))& Lower(right(c4,len(c4)-1))
    • What does this do?
• Find
  • =find(text to find, text to find it in, where to start looking)
  • This is case sensitive.
    • Search for "v" in valverde
    • Search for "V" in valverde
  • The third parameter is optional
    • But useful if we are looking for multiple occurrences of the substring.
    • Search for "v" in valverde after position 1
  • Split column K into two pieces on the "-", using text functions, no errors please.
  • There is a search function as well, which appears to be case insensitive.
• Substitue
  • =Substitute(text, old text, new text)
  • Let's replace all the stand alone N in the incident address line with North
  • Our authors show a few tricks with substitute
  • =len(text) - len(substitute(text, " ", ""))+1 gives the number of "words" in a string.
  • Count the number of words in the incident address.
• Cleaning text
  • trim will remove extra characters from the text
  • clean will remove non-printables from the string.
• char
  • =char(n)
  • inserts the ascii character for the given decimal number.
  • ="hello"&char(10)&"world"
  • Needs word wrap enabled in the cell.
  • But will be applied in charts.
• code
  • =code(str)
  • Returns the ascii code for the first letter of the string.
• Exact
  • =exact(s1,s2)
  • Checks to see if two strings are an exact match.
• Rept
  • =repeat(s,count)
  • Repeats s count times.
• value
  • Will attempt to turn a string into a number.
• Fixed, Dollar
  • Convert the number to text using a preset format
  • You have some control but not much.
• Text
  • =text(number, format)
  • Will convert a number to text using the format code.
  • See this article for some functions.
  • Better yet, explore format and custom format.