|Since there is a lack of consistency in the way the individual data strings are laid out, it's hard to come up with a single formula that can be entered once and dragged around to cover all situations.|
Year Graduated: 2008
If we assume that the 4 digit year will appear 2 characters after Year Graduated, then you can do this:
Enter Year Graduated in C1 and enter this in C2:
If you drag this down, it should return an empty cell in C2 (since there is no Year Graduated entry) and 2008 in C3.
License No. and State:
In this case, the license no. (and state) are not a fixed length. Finding the beginning of the string should be no problem becasue we know it will start after License No. and State:, but determining the length is a bit more difficult.
If the string was always followed by Supervisor as it is in B2, we could use that as the stop, but since Supervisor may not appear in every cell, we can't use that as the stopping point.
This will take some playing with. Let me see what I can do.
BTW, I hope you are satisfied with keeping the License No. and State together as one answer. Splitting those up adds another layer of complexity to the situation.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.