The following screen shot is of a MS-Excel 2010 practice exercise in string manipulation, which has proven quite handy when it comes to reformatting mailing labels recently.
A copy of this Excel spreadsheet can be found at:
String manipulation exercise – Scott Crump
The helpful formulas found in this exercise are:
1. =PROPER(A18) The proper formula capitalizes the first letter of both the Last Name, First Name and makes all the other letters lower case.
2. =TRIM(B18) The trim formula removes excess spaces between the Last Name, First Name configuration.
3. =FIND(“,”,C18) The find formula gives the numerical number of spaces that what ever is in the quotation marks to find is located in the specified cell. In this case we were looking for , in cell C18.
4. =LEFT(C18, D18-1) The left formula takes what is in the cell indicated, in this case C18, from the left hand side and then takes the numerical value found in the cell indicated, in this case D18, and in this case subtracts one space from that numerical value and provides the result of that formula in the designated cell, in this case E18.
5. =MID(C18,D18+2,99999) The mid formula takes what is in the cell indicated, in this case C18, from the midpoint and then takes the numerical value found in the cell indicated, in this case, D18, and in this case adds 2 spaces. The 99999 is the number of spaces you want returned since none of the names are longer than 9 digits this works. If the longest name would be more than 9 characters in length you would want to use that value instead.
6. =CONCATENATE(F18, ” “,E18) The concatenate formula is the most fun of these and I have had the pleasure of actually being able to put into practice with other spreadsheets recently. What the concatenate formula essentially does is takes the value of the two cells indicated by F18 and E18 and adds a space between them as indicated by ,” “, . This brings the first and last name into conventional format instead of the way we originally had it in last name, first name format.
I had a spreadsheet that had the mailing address split up into five separate cells one for Street Number, Direction, Street Name, Unit Type and Unit Number. The concatenate formula helped me bring all of these elements into one cell so that I could then use that information for a mail merge which I talk about that process in the MS-Word section of this portfolio document.
These excel formulas really do come in handy when working with large numbers of records in an excel spreadsheet. The more one works with mailing lists, the more this becomes apparent. Hope that it proves helpful for you as well.