Working with Text
Most people use Excel functions to calculate numbers. However, Excel also provides several very useful functions for manipulating text. These can be real time savers. This section illustrates some of the most common ways to manipulate text in Excel.
Parsing Text with Text Functions
Suppose, for example, that someone has given you a spreadsheet such as the one below. Each cell in column A has a person’s name: last name, then a comma and a space, and then first name. Your job is to parse (that is, separate) these names as indicated for the name in cells B1 and C1. That is, column B should have all of the first names and column C should have all of the last names. Unfortunately, imagine that there are 5000 names in column A. What do you do? If you just start typing, you’ll be at for a long time (and you’ll undoubtedly make mistakes).
Fortunately, there is a much better way. Logically, we observe that every name has a comma and a space. The first name is what comes after the comma and space, and the last name is what comes before it. For the name Jones, Bob, there are 10 characters (including the comma and space, and the comma and space are characters 6 and 7. So the first name is the rightmost 3 characters and the last name is the leftmost 5 characters. In any parsing operation, this is the first and probably the most crucial step: find a pattern.
Dostları ilə paylaş: |
