Need help with "if cell begins with" formula

[solarm]

Good morning,
I just joined the site so please excuse any rookie mistakes.

I'm trying to build a work around for a formula but it's proving to be more difficult than originally anticipated and would love some help.

Background; I'm looking to manipulate an Excel 10 output file from Salesforce.com that contains a text list of conversion points in column P. All conversion points have different text lengths. If there are multiple conversion points, they're separated by a semi-colon. Row 1 is headers.

I'm looking to do either one of two things in a new column (Q, for example);

1) Return a yes/no if the conversion point(s) in cell P2 *begins with* the value in Q1 (which will be a text value of a single conversion point name).

2) List the conversion point - or first, if it contains several - in column P.

Option two is the preferable option.

Thanks in advance for the help, and please let me know if I can provide any more clarity or examples.

Thanks,
Matt

 

[RomulusMilea]

Hi Matt,

To better understand your request, please insert 2 relevant examples (example 1 = one conversion point, and example 2 = at least 2 conversion points), I mean what do you have in column P and what you expect to get in column Q, with your desired formulae.

Does it make sense ?

Thank you.

 

[solarm]

Thanks for the reply.

Here is an example;

Conversion Points (Column P) ----- Desired Output (Column Q)
Manually Created ----- Manually Created
Manually Created; Mobil ----- Manually Created
Mobile; Whitepaper - AA; TM ----- Mobile
Express; Mobile ----- Express
Pricing; Express; Mobile ----- Pricing

Basically, return the results of column P, if there is a semi-colon (multiple conversions), return all values LEFT of ";".

Does this help?

 

[jfish1288]

If I understand correctly you just want the text left of the first semi colon? This should work for that.

=LEFT(P1,FIND(";",P1)-1)

 

[RomulusMilea]

Hi there,

Now it is much better . Try this formula in cell Q10, for instance:

=IF(P10="","No input data !",IF(LEN(P10)-LEN(SUBSTITUTE(P10,";",""))=0,P10,LEFT(P10,FIND(";",P10)-1)))

If P10 is empty formula will return "No data !".

If P10 will contain no ";" (this is done by LEN(P10)-LEN(SUBSTITUTE(P10,";","")=0) then formula will return just the content of P10 as it is.

If P10 contains at least 1 semi-colon, then formula will return entire string before first semi-colon.

Please confirm this solves your request.

Cheers,
Romulus.

 

[RomulusMilea]

JFish1288,

If a value in column P will not contain semi-colon, your formula will return an error, #VALUE!. Agree ?

 

[jfish1288]

Yes it will. Perhaps a better formula would have been

=IF(ISERROR(FIND(";",P1))=TRUE,P1,LEFT(P1,FIND(";",P1)-1))

or even better

=IF(P1="","",IF(ISERROR(FIND(";",P1))=TRUE,P1,LEFT(P1,FIND(";",P1)-1)))

 

[solarm]

Try this formula in cell Q10, for instance:

=IF(P10="","No input data !",IF(LEN(P10)-LEN(SUBSTITUTE(P10,";",""))=0,P10,LEFT(P10,FIND(";",P10)-1)))

Cheers,
Romulus.

Genius!

Thanks Romulus - I owe you a beer. That's seriously a terrific help.

 

[RomulusMilea]

Solarm,

You are welcome, do not mention it. I prefer dark beers . Have a great weekend, wherever you are.

Romulus.