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

 

[davesexcel]

Possibly,
=IFERROR(TRIM(MID(A1,FIND("###eWindow Add-on to ",A1)*LEN("###eWindow Add-on to "),999)),"")

 

[Mr2017]

Amazing!!

That worked! Thank you very much, @davesexcel !

 

[Mr2017]

Hi Dave

There's an entry with a slight variation.

So instead of

"###eWindow Add-on to Market Insight - Agriculture Package" it is

"eWindow Add-on to Market Insight Agriculture Package - CSM License" (without the three #'s at the beginning and with the characters "- CSM License" at the end.

Do you know if there's a formula that can strip out the "eWindow Add-on to " at the beginning in the "eWindow Add-on to Market Insight Agriculture Package - CSM License" example but also strip out the " - CSM License" at the end, please?

 

[Peter_SSs]

Does this do what you want?

20 10 20.xlsm
	A	B
1	###eWindow Add-on to Market Insight - Agriculture Package	Market Insight - Agriculture Package
2	eWindow Add-on to Market Insight Agriculture Package - CSM License	Market Insight Agriculture Package
3	###eWindow Add-on to Market Insight - Oil Package	Market Insight - Oil Package
Extract Text

Cell Formulas
Range	Formula
B1:B3	B1	=REPLACE(LEFT(A1,SEARCH(" - CSM License",A1&" - CSM License")-1),1, SEARCH("eWindow Add-on to",A1)+17,"")

 

[Mr2017]

Awesome!

Yes! That resolves both conditions!

Thanks @Peter_SSs!

 

[Mr2017]

Hi Peter

I've got a couple of quick questions, please?

1) What's the reason you added 17 on the end of the formula?

2) I'm trying to add those two conditions to an existing formula, but keep on getting a 'too few arguments' error.

The formula refers to row cell B3283 in the actual file, where the discrepancy first arose. I imagine that it must be a missing bracket somewhere?

If you're able to spot it please let me know.


=IFERROR(IF(OR(LEFT(b3283,21)="###eWindow Add-on to ",LEFT(b3283,18)="eWindow Add-on to "),REPLACE(LEFT(b3283,SEARCH(" - CSM License",b3283&" - CSM License")-1),1, SEARCH("eWindow Add-on to",b3283)+17)),TRIM(LEFT(B3823,FIND("(",B3823,1)-1)),"")

 

[Mr2017]

By the way, this "IF(OR" formula works.

ie if
A1 = "###eWindow Add-on to Market Insight - Agriculture Package"
A2 = "###eWindow Add-on to Market Insight - Oil Package"
A3 = "eWindow Add-on to Market Insight Agriculture Package - CSM License"

the formula below returns the correct results.

=IF(OR(LEFT(A1,21)="###eWindow Add-on to ",LEFT(A1,18)="eWindow Add-on to "),REPLACE(LEFT(A1,SEARCH(" - CSM License",A1&" - CSM License")-1),1, SEARCH("eWindow Add-on to",A1)+17,""))


However, if I replace with double quotes at the end of the formula ie "" with

TRIM(LEFT(A1,FIND("(",A1)-1))

I get a VALUE error.

But I'm not sure why? Any thoughts would be greatly appreciated, as the TRIM(LEFT(B3823,FIND("(",B3823,1)-1)) is there to account for all the other instances.

Then I'll add the IF(ERROR last to return a blank, if there is an error....

 

[Mr2017]

I managed to find a way around it using =IF(ISNUMBER(SEARCH

The trim function I wanted to add on at the end would only apply IF there was a bracket or parentheses in the text.

So I used =IF(ISNUMBER(SEARCH at the beginning to search for a bracket, then put the trim function after that, then put the other two conditions above, which you kindly helped me with.

So the combined formula looks like this:

=IF(ISNUMBER(SEARCH("(",A1)),TRIM(LEFT(A1,FIND("(",A1,1)-1)),IF(ISNUMBER(SEARCH("eWindow",A1)),REPLACE(LEFT(A1,SEARCH(" - CSM License",A1&" - CSM License")-1),1, SEARCH("eWindow Add-on to",A1)+17,"")))

Thank you both for your help.

 

[Mr2017]

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

Thank you in advance.