using a single quote ' in SEARCH function
 

I'm trying to see if the text in a cell contains the single quote (or
apostrophe). How can I put that in my SEARCH function. Surrounding the
single quotes with double quotes, like this SEARCH("'", A1) doesn't work. I
know there must be a forcing character to embed special characters in string
literals, but the documentation with 2007 Beta 2 is somewhat lacking, or I
just can't find it.
--
ExTexan

using a single quote ' in SEARCH function
 

ExTexan

Just wrapping the single in doubles or using a tilde ~ both work for me in
XL2007 RTM. The only time this won't work is if you have a single apostrophe
as the first character, as Excel uses this to signify text entry (You'll
also notice it doesn't show|) adding a second will return 1 from the SEARCH
function

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"ExTexan" wrote in message
...
I'm trying to see if the text in a cell contains the single quote (or
apostrophe). How can I put that in my SEARCH function. Surrounding the
single quotes with double quotes, like this SEARCH("'", A1) doesn't work.
I
know there must be a forcing character to embed special characters in
string
literals, but the documentation with 2007 Beta 2 is somewhat lacking, or I
just can't find it.
--
ExTexan

using a single quote ' in SEARCH function
 

If you ned to locate cells with leading apostrophes and don't mind a little
VBA, then enter and run:

Sub leading_tick_locator()
Dim r As Range, rSel As Range
For Each r In ActiveSheet.UsedRange
If r.PrefixCharacter = "'" Then
If rSel Is Nothing Then
Set rSel = r
Else
Set rSel = Union(rSel, r)
End If
End If
Next
If Not rSel Is Nothing Then
rSel.Select
End If
End Sub



It will select all cells with leading apostrophes and ignore embedded
apostrophes.

Have a pleasant weekend!
--
Gary's Student


"ExTexan" wrote:

I'm trying to see if the text in a cell contains the single quote (or
apostrophe). How can I put that in my SEARCH function. Surrounding the
single quotes with double quotes, like this SEARCH("'", A1) doesn't work. I
know there must be a forcing character to embed special characters in string
literals, but the documentation with 2007 Beta 2 is somewhat lacking, or I
just can't find it.
--
ExTexan