14. RIGHT & Text-to-Columns
Imagine that your boss just sent you a file with 100,000 names and addresses compiled from several data dumps off multiple different database systems, such as Oracle RDBMS, SAP Sybase ASE, Informix, MongoDB, Redis, and Couchbase. The file is in a CSV (comma separated values) delimited format, so you can open it in Excel, but the delimiters are all over the place—some are commas, some are spaces, some are tabs, and so on. Your job is to reformat the run-on data into five fields: Name, Address1, Address2, City, and State.
A. First, separate the city, state, and ZIP codes into different columns. Select column C (City, State, ZIP), then choose Data > Text to Columns. Ensure the Delimited button is checked, then click Next. On the next screen, ensure that the Comma box is checked, then click Next. Browse through the list to ensure the separation is correct, then click Finish. Excel divides the one column into two.
B. Now we need to separate the ZIP codes from the state names. For this task, the Text to Columns option would not be accurate, because the only delimiter available is a space. Because some of the states have multiple names such as Prince Edward Island, the Text to Columns function would spread the data across too many columns.
C. The solution is to use the =RIGHT function. Because some of the ZIP codes have five digits and some have six, enter the following command in cell E2: =RIGHT(D2,6), then copy from E2 down to E3 through E16. Done. Column E now contains ZIP codes only. However, column D still has both states and ZIP codes.
15 & 16. LEFT & LEN
Use this function to separate the states from the ZIP codes. Enter the following formula in cell F2: =LEFT(D2,LEN(D2)-6), then copy down from F2 to F3 through F16. Basically this formula says go to cell D2, count 6 places from the left and remove those characters, leaving the remaining characters in cell D2 (regardless of how many). As long as one piece of information inside a cell is unique, you can use that information to add, delete, or replace other data inside that cell.
To move or manipulate the data in columns E or F (which contain formulas), highlight the column and choose Copy, move cursor to another column, and choose Paste Special > Values, then click OK.
Use this handy function to determine how much your payments would be on that new car you just took for a test drive. The process is simple: Enter the function followed by the interest rate divided by 12 (for 12 months), the Term (or number of monthly payments), followed by the Loan Amount.
Enter this formula in cell F2: =PMT(D2/12,E2,C2,0,0), then copy from F2 down to F3 through F6.
To calculate a down payment, enter the down payment percentage in column F; enter the formula in column G; enter the adjusted loan amount formula in column H; and the new monthly payment formula in column I. Also, change the Term (in months) to Term (in years) in column E—for example, instead of 360 months for the House Loan, enter 30 years.
Enter the following formula in cell G10: =SUM(C10*F10). Enter this formula in cell H10: =SUM(C10-G10). And enter this formula in cell I10: =PMT(D10/12,E10*12,H10,0,0). Note that the Interest Rate is still divided by 12, and also, that the Term (in years) is multiplied by 12.
Handy tip: Ever wonder why some currency formats center the dollar amount and the dollar sign ($1500.00), while others push the numbers aligned to the right and push the dollar sign aligned to the left ($ 1500.00)? To center the number and dollar sign, highlight the cell, and choose Home tab > Number group > Number Format > Currency. To align the dollar sign left and the numbers right, highlight the cell, then click the $ symbol in the Number group.