Split text into columns help

So, the example you gave is actually a rare one where using a space as a delimiter would work fine. You would get data in columns A:J and in column K, you could put

Formula:

Please Login or Register  to view this content.

, etc to gather them up. If you have more data, then I'd need to see that, because I can't tell certain things like, is there ever a middle name for the Account Manager, etc.

With the first text string in A1:

This in B1 will give you title:

Formula:

Please Login or Register  to view this content.

This in C1 will give the account manager's name:

Formula:

Please Login or Register  to view this content.

This in D1 will give the piece in capitals:

Formula:

Please Login or Register  to view this content.

This in E1 will give the date:

Formula:

Please Login or Register  to view this content.

and this in F1 will give the Function number:

Formula:

Please Login or Register  to view this content.

Provided all titles are account manager (in which case I'm not sure why you need that in a separate column), there are no middle initials or names for the account managers, there are no double spaces prior to the capitalised section, that all dates are in the same format and that all function numbers are 6 characters long.

I am assuming you're familiar with the LEFT, RIGHT and LEN functions from your OP. The MID function takes the mid part of a text string from a starting position you specify and a length you specify =MID(Text string,Start position,Length).

It then comes down to how to best identify each of the sections of the text string. The FIND and SEARCH functions give the starting position (number of characters from the left of the text string) of the text searched.

The account manager's name was found by taking from the 17th character (with the previous 16 being "Account Mamager " including the spaces) until the 4th space from the front of the text string. Looking for the 4th instance of a space is why I said there could be no middle names or initials and no double spaces as that would mean the 4th space was not after the name. I used the SUBSTITUTE function to replace the 4th space with a character not used in the rest of the text. I chose the | character. I then used the FIND function to find the position of that character. Length of text to return is the position of the 4th space less the 16 characters of "Account Mamager " plus one for the 4th space itself, which is why there is the -17 at the end of the formula.

The capitalised section starts from the first character after the 4th space. This is the same as the length (LEN) of the name plus the length of "Account Mamager " plus the 4th space plus 1, or simply 18+LEN(C1). I could also have used FIND("|",SUBSTITUTE(A1," ","|",4))+1 instead. The length of text to return is until 4 characters before the date (" on "). The dates all had 2 hyphens (-) separated by 3 characters, so searching for "-???-" identified it's location. You need to use the SEARCH function to use wildcard characters. The total length to return is from the start of the text string to the position "-???-", less the length of the name, less the length of "Account Mamager ", less the length of " on DD" where DD is the first 2 digits of the date, or more simply SEARCH("-???-",A1)-(24+LEN(C1)). Again there is an alternative, being SEARCH("-???-",A1)-(FIND("|",SUBSTITUTE(A1," ","|",4))+7). As I was writing this explanation I realised you could also have searched for " on " instead of "-???-". If you did that the alternatives for the lengths to use in the MID function would be FIND(" on ",A1)-(18+LEN(C1)) or FIND(" on ",A1)-(FIND("|",SUBSTITUTE(A1," ","|",4))+1).

The date itself was again found by searching for the "-???-" and moving back 2 spaces for the DD. The length of the text to return is always nine as the format is always DD-MMM-YY, or 9 characters.

Hopefully that makes sense to you but if you have any further questions let me know.