I am trying to locate cells that contain an apostrophe as the first character
in the cell. When using Excel's Find command, I input "'" (double quotes,
apostrophe, double quotes) as the search criteria, but Excel can't seem to
find any (even though I know for a fact that there are cells that contain an
apostrophe).
Can anyone tell me what search criteria I should be using?
Thanks,
Bob

Hi Bob,

Use a tilde in your find criteria like this. ~'
Forget the double quotes.

HTH
Martin

hi, Bob.
Do you know that ' is a escape character in excel?
That is, if you want to input a value in which ' is the first character, you
need to enter '' (two apostrophes) for one ', and ''' for two ', and so on.
Here, the first one of ' is used for escape character.
Well, if you want to find character ' with the find dialog box, you needn't
to use "'", just use '.
Hope it helps.



"Bob" wrote:

I am trying to locate cells that contain an apostrophe as the first character
in the cell. When using Excel's Find command, I input "'" (double quotes,
apostrophe, double quotes) as the search criteria, but Excel can't seem to
find any (even though I know for a fact that there are cells that contain an
apostrophe).
Can anyone tell me what search criteria I should be using?
Thanks,
Bob

Martin,
Thanks for the suggestion. Unfortunately, your search criteria does not
find cells that contain an apostrophe as the FIRST character in the cell
(which is what I'm trying to find).
Bob


"MartinW" wrote:

Hi Bob,

Use a tilde in your find criteria like this. ~'
Forget the double quotes.

HTH
Martin

This little macro will locate all cells that start with an apostrophe:


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



--
Gary's Student


"Bob" wrote:

I am trying to locate cells that contain an apostrophe as the first character
in the cell. When using Excel's Find command, I input "'" (double quotes,
apostrophe, double quotes) as the search criteria, but Excel can't seem to
find any (even though I know for a fact that there are cells that contain an
apostrophe).
Can anyone tell me what search criteria I should be using?
Thanks,
Bob

Actually, the ' character I am trying to find is a formatting code (which
appears in the formula bar but not in the cell). Go to any cell and type '
(apostrophe) and then press Enter, and you'll see what I mean.
Bob


"tarzan" wrote:

hi, Bob.
Do you know that ' is a escape character in excel?
That is, if you want to input a value in which ' is the first character, you
need to enter '' (two apostrophes) for one ', and ''' for two ', and so on.
Here, the first one of ' is used for escape character.
Well, if you want to find character ' with the find dialog box, you needn't
to use "'", just use '.
Hope it helps.



"Bob" wrote:

I am trying to locate cells that contain an apostrophe as the first character
in the cell. When using Excel's Find command, I input "'" (double quotes,
apostrophe, double quotes) as the search criteria, but Excel can't seem to
find any (even though I know for a fact that there are cells that contain an
apostrophe).
Can anyone tell me what search criteria I should be using?
Thanks,
Bob

Thanks for the helpful macro. However, I was hoping to find the ' character
(i.e., the one that is used as a formatting code and appears in the formula
bar but not in the cell) using Excel's Find command. Any suggestions?
Thanks again,
Bob


"Gary''s Student" wrote:

This little macro will locate all cells that start with an apostrophe:


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



--
Gary's Student


"Bob" wrote:

I am trying to locate cells that contain an apostrophe as the first character
in the cell. When using Excel's Find command, I input "'" (double quotes,
apostrophe, double quotes) as the search criteria, but Excel can't seem to
find any (even though I know for a fact that there are cells that contain an
apostrophe).
Can anyone tell me what search criteria I should be using?
Thanks,
Bob

You have already discovered that using Find/Replace to locate leading
apostrophes is very difficult because the leading apostrophe isn't really
part of the text string. It does not contribute to the length of the string,
etc.

I don't know a way to locate the things with resorting to VBA.
--
Gary's Student


"Bob" wrote:

Thanks for the helpful macro. However, I was hoping to find the ' character
(i.e., the one that is used as a formatting code and appears in the formula
bar but not in the cell) using Excel's Find command. Any suggestions?
Thanks again,
Bob


"Gary''s Student" wrote:

This little macro will locate all cells that start with an apostrophe:


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



--
Gary's Student


"Bob" wrote:

I am trying to locate cells that contain an apostrophe as the first character
in the cell. When using Excel's Find command, I input "'" (double quotes,
apostrophe, double quotes) as the search criteria, but Excel can't seem to
find any (even though I know for a fact that there are cells that contain an
apostrophe).
Can anyone tell me what search criteria I should be using?
Thanks,
Bob