How to detect leading or trailing apostrophe and quote characters

[kyjae]

Hi All,

I've not had a lot of luck trying to resolve this via Google searches so I'm opening this one out to you guys.

The problem is simple. I want to alert a user that he or she has typed in a text string that begins or ends with an apostrophe ' or begins or ends with a quotation mark " so that they can take corrective action in removing these characters. The reasons for this are superflous to this post but what I thought was a logical answer returns a formula error. Here's the scenario:-

A user enters a string of text in cell A1.

Cell B1 checks if the text, if any, in A1 begins or ends with a ' or a " and if it does, returns an "Error" message

The formula I've tried in B1 is =IF(OR(LEFT(A1,1)="'",LEFT(A1,1)=""",RIGHT(A1,1)="'",RIGHT(A1,1)=""")),"Error","Ok") but Excel will have none of it

Has anyone come across this scenario and found a solution.

Many thanks.

[XOR LX]

Hi,

One of the issues you have is that Excel treats a leading single apostrophe as indicating that that cell's contents should be treated as text, though the single apostrophe itself is not physically entered as part of the text.

How are you accounting for this in your set-up? Do you mean it's possible that users may enter two leading apostrophes?

Regards

[kyjae]

Hi

Thanks for replying. It actually doesn't matter how many leading or trailing apostrophes are entered, just the fact that an apostrophe exists at the start or end of the text so the user can remove it from the text string. Is there a way of bypassing how Excel treats a leading apostrophe so that the user is alerted?

[kyjae]

Hi

Thanks for replying. It actually doesn't matter how many leading or trailing apostrophes are entered, just the fact that an apostrophe exists at the start or end of the text so the user can remove it from the text string. Is there a way of bypassing how Excel treats a leading apostrophe so that the user is alerted?

[XOR LX]

Try:

=IF(OR(LEFT(A1)={"""","'"},RIGHT(A1)={"""","'"}),"Error","Ok")

or

=IF(OR(MID(A1,LEN(A1)^{0;1},1)={"""","'"}),"Error","Ok")

Regards

[kyjae]

[Duplicate posted in error - irgnore]

[kyjae]

Thanks,
I'll try the formulae you've suggested. Would you be able to explain the logic behind the forlmulae as I'm not familair with these functions or the use of curly brackets.

[XOR LX]

It's just useful to condense the formula slightly. The first could equally be written:

=IF(OR(LEFT(A1)="""",LEFT(A1)="'",RIGHT(A1)="""",RIGHT(A1)="'"),"Error","Ok")

if you find it easier to understand.

Regards

[kyjae]

All sorted. Many thanks.

[XOR LX]

You're welcome.