Excel is a powerful tool for keeping accounts or creating statistical charts. Its function library contains many mathematical utilities to ease the collation of financial and statistical data. However, the package also contains many useful text functions. A number of these functions can enable you to remove text after a given character.
The LEFT function will return only the first few characters of a given word. If you want to remove everything after a character in specific position in a word, then left will perform this task for you. Try the function by opening Excel with a new spreadsheet. Type into B1 the following function (without the quotes): “=left(A1,5)” then press the enter key. Click in A1 and type any word longer than five characters and hit the “Enter” key. B1 will show the same word but with all characters after the fifth character removed.
The MID function performs the same task as left, only it can extract a section of text from the middle of another piece of text. It requires three parameters: a piece of text, a starting point and the number of characters to extract. By setting the starting point to 1, MID can be made to produce the same results as left. In cell B2 type “=mid(A1,1,5)” (without the quotes) and hit the “Enter” key. This will show the same output as the example used for left. That is, it will remove all characters after the fifth character.
LEFT and MID are the best methods to remove characters after a given position. They can be combined with the SEARCH function if you want to remove all characters after a given character no matter where in the text that character lies. SEARCH will locate the first instance of that character. Type into cell B3 the formula “=left(A1,search(“b”, A1,1)” (without the quotes) and press the “Enter” key. Search is not case sensitive, but it returns a “#VALUE” error if the search string does not appear in the source text, which can look messy. Search can also locate a piece of text within a text.
The FIND function does exactly the same as SEARCH. Try this function with the MID function by typing into cell B4 “=mid(A1,1,find(“b”,A1,1))” (without the quotes) and press the “Enter” key. This will cut off all characters after the first instance of the letter “b” in the text you type into A1. Like SEARCH, find can also locate the start position of a piece of text within text. The only difference between SEARCH and FIND is that FIND is case sensitive.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for