Here are some cool tricks when dealing with formuals that manipulate text.

  1. You can use the & operator to combine text and formulas or other cells to make a string of text.  If I wanted to display in A 200 The Grand Total Is 122 and 122 calculated in H435, I no longer need to place the text in cell A200 and link the cell H435 in B200.
    Simplye type in A200:    =”The Grand Total Is ” & H435
    There is a space after the word Is. The value 122 is treated as text even if it is formated as currency or accounting. The result is The Grand Total Is 122
  2. To display the number as currency with the string:
    Simply type in A200:   =”The Grand Total Is ” & text(H435,”$#,###.00″)
    The result is The Grand Total Is $122.00
  3. You can also use anyformula rather than referencing a cell.
    Simple type =”The largest value in column M is ” & max(m:m)
  4. You can also use a formula in step 2 as well.
    =”The Grand Total Is ” &  text(sum(H200:H430),”$#,###.00″) 0r
    =”The Grand Total Is “&DOLLAR(H435,2)
    The 2 represents the number of decimal places.
  5. The REPT function repeats a text string any number of times.
    =REPT(“-“,30)
    This will repeat the dash (-) 30 times across a cell.
  6. To remove excess spaces or nonprinting characters use the following text functions:
    TRIM: This functin removes all leading and trailing dspaces and replaces internal string of multple spaces with wsincle space.
    =TRIM(A1)
    CLEAN: This function removes all nonprinting characters from a string.  These nonprinting characters appear in the cells when the information was imported from an outside source.
    =CLEAN(A1)
  7. There are times when you received text that are all in caps.  You can use the PROPER function to convert the text in proper case. But all the first letter of all the words in the cell will be in proper case. What if you want to only  have the first letter in upper case and the rest in lower case? For example, in cell A1 contains the words FIRST QUARTER. Use the LEFT function to extract the first letter and then convert it as uppercase. Then concatenate the rest of the string that will use the RIGHT function to extract all but the first letter and convert it these letters to lowercase.
    RIGHT returns a specified number of character from the end of a string.
    LEFT returns a specified number of characters from the beginning of a string. LEFT(A1,5) —> FIRST or LEFT(A1) —> F
    LEN returns the number of characters in that string.=(LEFT(A1)) & RIGHT(LOWER(A1), LEN(A1)-1) —>First quarterNotice that the LEFT function will return the value F.
    LEN(a1) retruns 13 then subtract which is 12. Then from the right count 12 and use the LOWER function —>irst quarter.
  8. Here are the formulas that will separate the first name, last name, and middle initial in column B, C and D.
    In B1 for the First Name
    =LEFT(A2,FIND(” “,A2)-1)
    In C1 for the Middle Initial
    =IF(LEN(B2&D2)+2>=LEN(A2),””,MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2&D2)-2))
    In D1 for the Last Name
    =RIGHT(A2,LEN(A2)-FIND(“”*””,SUBSTITUTE(A2,”” “”,””*””,LEN(A2)-LEN(SUBSTITUTE(A2,”” “”,””””)))))”

    Text Strings

Advertisements