Have you had to work with multiple files or data sources to put together mailing or customer lists or to validate data? Pulled your hair out trying to clean up spreadsheets or database exports into something that’s searchable and organized? If this sounds familiar, you may have wished you’d considered how Excel, and more specifically Excel text functions, can help you resolve these issues.
Excel has many text functions, some of the more popular and frequently used are the LEFT, RIGHT, MID, LEN or FIND functions. Read Slice and dice – five Excel text functions to make your life easier in the winter 2011 issue of Update magazine for examples of how to use these functions. Want more practice using text functions? Try the following practice workbook example (scroll to the download button at the bottom of the page).
Common uses of text functions in Excel include:
- Return text up to the second space in Microsoft Excel
- Find if a character is in a cell in Microsoft Excel
- Find if a character is in a range in Microsoft Excel
- Find the most commonly repeated string in a column in Microsoft Excel
- Parsing text to the right of a search criteria – delimiter in Microsoft Excel
- Count the number of times in particular date a text appear in adjacent column in Microsoft Excel
- Using Cell Reference with Keyboard signs in the Criteria Range argument when using COUNTIF formula.
- Make LEFT Function recognize a number
- Calculating the Average of Every Nth Value, Excluding Zeros
- Removing Redundant Characters from a Range of Cells and Resetting the Format
- Avoiding Problems when Numeric Values are used as Text
- Counting number of unique entries in a specified range.
- Splitting a Full Address into Three Separate Cells
- Creating New IP Addresses
- Counting the Number of Excel Files in a List According to File Type and Date Criteria
- Altering results returned in case of an error.
- Retrieve Value Using Case-Sensitive Lookups
- Creating email address
- Separating a Number Into Digits
- Separtaing A String Into Substrings.
- Restricting Cell Entries to Text Only
- Restricting the Number of Characters Entered into a Cell
- Preventing Duplicates When Entering Data
- Combining Text and Formatted Numbers into a Single Entity
- Combining Text and a Formatted Date into a Single Entity
- Combining Numbers that Have Leading Zeros
Have an Excel tip to share? Leave a comment today!
