For given example, the string to extract the name from is
'Contractor Stephen McWhinnie 14 hours w/e 30-Sep-23'
It looks like you want to get the name following some code string in source string, e.g. 'Contractor '. Now the conditions you haven't detailed:
1. Is there a single code string ('Contractor) indicating that the name will follow, or there may be several different ones?
2. Is the source string always starting with code string, or the code string may be placed anywhere in source string (like '<Some text here>, contractor Joe Smith ...')?
3. I will assume, that the name you want to extract always starts immediately after code string (otherwise it will be impossible to determine, from where the name starts), but how is determined, where it ends? Unless you have some other code string indicating the end of name (like ','), or you have the name always composed from single forename and single surname - i.e. the name always has a single space in it - there is no way to decide. From your example it looks like you have any numeric character ('1' in your example) in source string as second code string. I.e. the name string (probably with space in end) ends when any of numeric characters '0', '1', ..., '9' appears in source string.
Assuming that you want to extract only contractors, placed anywhere in field, and having contractor name followed with space, and then with some number, in attached is an example how to do this (I used additional calculated columns for this formula to calculate the start position of name, and the name length - because having the formula for start position of name to be inserted 10 times to calculate the length of it would surely exceed the limit for formula length).
|