Text lookup

[ryansm05]

Hi,

I need to create a formula that looks at the mid point of a cell =MID(CellA1,20,????) and starts at character 20 and returns the next 2 words before a space.

Cell A1 contains "AAAAAAAAAAAAAAAAAA RyanSmith Recruitment AFH 0967"
Cell A2 contains "AAAAAAAAAAAAAAAAAA JonBlot Recruitment AFH 0987"

Formula in cell A1 to return: RyanSmith Recruitment
Formula in cell A2 to return: JonBlot Recruitment

There will be hundreds of lines of data and the character count will vary. Therefore, I need the formula to start at character 20 and return the next two full words before a second space.

If anyone can help it'd be much appreciated.

Thanks
Ryan

 

[tyija1995]

If they are always prefixed the same then try this formula:

TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(2-1)*LEN(A1)+1,2*LEN(A1)))

 

[ryansm05]

If they are always prefixed the same then try this formula:

TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(2-1)*LEN(A1)+1,2*LEN(A1)))

This works great for the two examples I shared. However, the 'aaaaaaaaaaaaaaaaaa' may actually comprise of 2/3/4/5 words (there are a lot of variations) - and so I need the formula to ignore what's in front of it effectively and then start from character 20 if possible.

Do you think this can be achieved?

Thanks
Ryan

 

[tyija1995]

Ahh OK that shouldn't be too much of a problem then as they all start at the 20th character, try:

=TRIM(LEFT(SUBSTITUTE(MID(A1,20,LEN(A1))," ",REPT(" ",LEN(A1))),2*LEN(A1)))

 

[DanteAmor]

Another idea:

=LEFT(REPLACE(A1,1,19,""),FIND(" ",A1,FIND(" ",A1,21)+1)-20)

 

[Peter_SSs]

Ryan
It is always a good idea to be realistic in the variety of sample data that you provide. You have now told us that there can be several 'words' in that first 19 characters but what about the last part? In your two samples they both have exactly 4 'words' after the 19th character and exactly 9 characters after the two words you want to extract. If that was consistent then this would suffice.

=MID(A1,20,LEN(A1)-28)