If you want your formula result to be a number, then use the VALUE function. If you want the formula result to be text, use the TEXT function.
Here is the syntax for each:
- TEXT(value,format_text) -- The format_text value can be any format from the Category box on the Number tab (in the Format Cells dialog box) except General.
Here is an example of each:
- =VALUE(MID(A2,46,FIND("&",A2)-46)) -- this actually finds a number beginning with the 46th character of a string, and continues until it find the ampersand character. By wrapping the formula with VALUE, I can now compare the number to other numbers in my lookup table.
- =TEXT(IF(ISERROR(INDEX('Language Lookup'!$B$2:$B$37,MATCH(B2,'Language Lookup'!$A$2:$A$37,0))),"NA",INDEX('Language Lookup'!$B$2:$B$37,MATCH(B2,'Language Lookup'!$A$2:$A$37,0))),"#") -- This uses a formula I posted May 15, 2008 that is doing a lookup. Remember the syntax above, I use "#" to specify the format of my text.