How to use Locate when a value has an apostrophe

Question & Answer
, ,
1

I’m trying to use the locate function to find the values for a field that has some values containing an apostrophe. Below is a entry. The s Hotel portion is in Black because it’s outside the first two apostrophes. How can I fix this?

 locate('The Lightkeeper's Hotel',{Hotel Name}) > 0,
'Region 5',
2

Hi @Emathis2007

I could use the double quotes to enclose the literal string
image

Result is as follows

image
image879×364 22.5 KB

3

Hi @Emathis2007,
It’s been awhile since we last heard from you, did you have any additional questions or were you able to find a solution for your case?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

4

Hi Brett, sorry about that. I was able to get my results by locating another field that didn’t use the names.
I went back and tried the double quotes again and figured out I can’t add this hotel in a list to locate the hotel, I have to separate this one before the double quotes work. Which will be my solution should I need this type of calculation in the future. See below:

ifelse
(

locate(‘The Aloft, The Beach House’,BranchName) > 0,
‘Region 1’,
locate(“The Lightkeeper’s Inn”,BranchName) > 0,
‘Region 1’,

'Other'

)

Thanks for your reply and followup. Your guidance helped me figure out to just separate the one item so the calculation works correctly.

1 Like