How to Use the FIND, FIXED, and LEFT Text Functions to Clean Data in Excel
One problem you may run into when you import data into Excel is that your text labels may not look right. You can keep your data clean with the following text functions.
The FIND function
The FIND function finds the starting character position of one text string within another text string. For example, if you want to know at what position within a text string the two-letter state abbreviation WA starts, you could use the FIND function.
The FIND function uses the following syntax:
The find_text argument is the text that you’re looking for. The within_text argument identifies where or what you’re searching. The start_num argument tells Excel at what point within the string it should begin its search. For example, to find at what point the two-letter state abbreviation WA begins in the string Redmond WA 98052, use the following formula:
FIND("WA","Redmond WA 98052",1)
The function returns the value 9 because WA begins at the ninth position (because spaces are counted).
The start_num function argument is optional. If you omit this argument, Excel begins searching at the very beginning of the string.
The FIXED function
The FIXED function rounds a value to specified precision and then converts the rounded value to text. The function uses the following syntax:
The number argument supplies the value that you want to round and convert to text. The optional decimals argument tells Excel how many places to the right of the decimal point that you want to round. The optional no_commas argument needs to be either 1 (if you want commas) or 0 (if you don’t want commas) in the returned text.
For example, to round to a whole number and convert to text the value 1234.56789, use the following formula:
The function returns the text 1,235.
The LEFT function
The LEFT function returns a specified number of characters from the left end of a text string. The function uses the following syntax:
The text argument either supplies the text string or references the cell holding the text string. The optional num_chars argument tells Excel how many characters to grab.
For example, to grab the leftmost seven characters from the text string Redmond WA, use the following formula:
The function returns the text Redmond.