Ask Your Question

How to find cells that contain words that begin with a (specific) letter ("A" e.g.)?

asked 2019-08-11 08:24:00 +0100

Isko106 gravatar image

updated 2020-09-03 15:49:47 +0100

Alex Kemp gravatar image

If I want to see all the cells which contain a word that begins with "A" for example, how do I do it?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-08-11 11:40:44 +0100

Opaque gravatar image

updated 2019-08-11 13:05:02 +0100


use Edit -> Find & Replace

Find: ^A
Options: Regular expressions

and click button Find all. You'll get a result list of all cells containing a value starting with "A"

image description

Update 1 According to the semantic of the question and with repect to the comment of @Mike Kaganski use:
Find: \bA

Update 2 If you want to find words beginning with an aribitrary letter and with respect to comment of @Lupp use:
Find: \b[:letter:]

edit flag offensive delete link more


In 'Search Results' box, it's better than MS Excel.

lonk gravatar imagelonk ( 2019-08-11 11:49:31 +0100 )edit

Given the wording of the question ("I want to see all the cells which contain a word that begins with "A" for example"), the regex would be rather \bA. See ICU regex documentation.

Mike Kaganski gravatar imageMike Kaganski ( 2019-08-11 11:51:09 +0100 )edit

Sorry - my bias.

Opaque gravatar imageOpaque ( 2019-08-11 11:56:05 +0100 )edit

I think ^A will only find cells where the entire content begins with A. To search for words beginning with A anywhere in the cell, try \bA where \b represents the beginning of a word boundary.

Already mentioned!

robleyd gravatar imagerobleyd ( 2019-08-11 12:26:21 +0100 )edit

@robleyd yes - this was what I meant by my bias to interpret as entire content begins with A (I'm too much used to only search for lines beginning with certain characters on *NIX command lines).

Opaque gravatar imageOpaque ( 2019-08-11 12:33:18 +0100 )edit

What about using \b[:letter:] (any letter alowed) or \bA (only letter "A" accepted) as the 'Find' RegEx?
@Opaque: If you also think my suggestion is valid, you may edit your answer with a respecive completion.

Lupp gravatar imageLupp ( 2019-08-11 12:55:08 +0100 )edit

@Lupp: aside from the discussion already happened in the comments, and the edit already done to the answer - the \b[:letter:] seem to serve no valid purpose: "find all words starting with a word" looks like a tautology...

Mike Kaganski gravatar imageMike Kaganski ( 2019-08-11 13:00:36 +0100 )edit

@Lupp Since there are very rare case where people posting questions with a clear motivation what they want to achieve, I try (more or less successfully;-)) to stick with the question but of course your suggestion is valid. Will update...

Opaque gravatar imageOpaque ( 2019-08-11 13:02:47 +0100 )edit

You (the OriginalPoster) may find that the meaning of the term "word" depends on what characters or text positions are supposed to delimit a word. See and for more detail.

Lupp gravatar imageLupp ( 2019-08-11 13:13:38 +0100 )edit

Quoting @Mike Kaganski: <>..."find all words starting with a word" looks like a tautology...
I would assume that's not what the suggested Regex does. Most or all common RegEx engines imply a technically extended meaning of the term "word" which includes (at least) to consist of latin letters, decimal digits, and the underscore. Since the subject of the question did not mention the specific character (I edited it later), but only the text mentioned it, I wanted to give a solution addressing the unedited subject, too, and I put the completements to the original subjects into parentheses.
This was intended to help visitors not interested in the specific letter to also find what they looked for.

Lupp gravatar imageLupp ( 2019-08-11 13:25:19 +0100 )edit
Login/Signup to Answer

Question Tools



Asked: 2019-08-11 08:24:00 +0100

Seen: 200 times

Last updated: Aug 11 '19