If the first 21 characters of a cell have specific text, then take everything to the right of that....

[Mr2017]

Hi

I have a cell where if the first 21 characters of the cell contain the text "###eWindow Add-on to " then I would like to take everything to the right of the last space in that phrase.

So if cell A1 had "###eWindow Add-on to Market Insight - Agriculture Package" then cell B1 would return "Market Insight - Agriculture Package"

If cell A2 has "###eWindow Add-on to Market Insight - Oil Package" then cell B2 would return "Market Insight - Oil Package"

Has anyone done this before, please?


###eWindow Add-on to Market Insight - Agriculture Package

 

[Peter_SSs]

It would still be good to understand why the number 17 was used at the end, however.

The formula is using the REPLACE function
=REPLACE(LEFT(A1,SEARCH(" - CSM License",A1&" - CSM License")-1),1, SEARCH("eWindow Add-on to",A1)+17,"")

REPLACE has this syntax
REPLACE(old_text, start_num, num_chars, new_text)

Using this text as an example
###eWindow Add-on to Market Insight - Agriculture Package
This text is the old_text since the black bold part of the formula simply strips off the " - CSM License" if it was on the end.

The SEARCH function finds the starting position of "eWindow Add-on to" which is position 4 (underlined) in the cell's text. However we want to replace all characters from the start of the text up to the end of "eWindow Add-on to ". This text is 18 characters long but since the search has already accounted for the first 4 characters, we only need to remove another 17 to get to the end of what is to be replaced.

Hope that made some sense.

 

[Mr2017]

Ok, great!

Thanks for clarifying that, @Peter_SSs!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.