Left(text, [num chars])
Mid(text, start_num, num_chars)
Search(find_text, within_text, [start_num])
and the symbol used to concatenate values/strings: "&"
Here's the dataset:
Here we have the names list by LastName,FirstName and the ID#'s.
The assignment: "Provide Initials (First then Last) and the last 4 digits in the ID#"
**For this example, we're going to assume all of the names are in the exact same format down every row.
In Cell C1 we'll provide a column header of "New ID". In cell C2 we'll go to the formula bar and begin with our formula. Since the First initial is in the MIDDLE of the text string and and be found after a comma, we'll use the Mid() and Search() formulas here.
For the MID() formula we need to assign values to the following requirements -
- text: the text we're referencing is the Name column and found in cell A2
- start_num: we need to search for the "," and start with the character immediately after it (+1)
- Search requires the following to be addressed:
- Find_text or Text to search for: "," (include the "" around the comma in the formula for this one)
- within_text: cell A2
- [start_num]: "1" - this is the first character in the cell A2
- num_chars: 1 - we only want the first letter of the first name
This returns "S" in cell C2. Next, we need the First letter of the Last Name. This one is a bit more simple since it is the beginning letter of the text string. We'll use the LEFT() formula
For the LEFT() formula, we need to assign values to the following requirements:
- Text: A2
- [num_chars]: 1
- Text: B2 - not A2, because the numbers are in B2!
- [num_chars]: 4
The task I had to accomplish was a bit more complicated involving dates of service as well. The last step that you would want to do is validate that the "New ID" was truly unique across your data set. To accomplish this, you could simply create a pivot table, add the "New ID" and then Name as the Row values and add New ID to the values section as a count of New ID. This would quickly show if you have any duplicates by listing multiple names under the same ID.
Hope you enjoyed this tutorial! Instead of offering to donate lots of your money to me, let other people do it by clicking on an advertisement to the right!