I need some assistance with developing a >> customer delimiter (text to columns) << function.
As you know, Excel provides a built-in capability that allows to use "text to columns" (either based on character delimitation or fixed width).
Based on the data set I have (with hundreds of records), the fixed width does not work for me... length of values varies for all records. Also, a delimiter equal to "." (single character) won't work that great given that some customers have additional details in, e.g., their names (prefixes, suffixes, etc.). Thus, alike values are across a number of columns.
Please see attached XLS with only 3 sample (mock) records. My question/requirements:
1. How can I develop a macro/module that would look at entire words/string vs. single-character delimitation?
2. However, given the variety of, e.g., organization names, I would need to be able to have several string values in a single module. That is, instead of running them sequentially, I would want to use some form of "OR" statement for a single run (e.g., "US Army" or "US Air Force" or "US Navy").
3. Then, once all records have been separated by these 3 values, I certainly could re-run the module based on different/multiple criteria.
Any idea as to how this could be accomplished? Again, please see attached XLS for additional details.