MrExcel.com - Your One Stop for Excel Tips & Solutions

Searching for reg. expression in text

Posted by Daniel Celotta on January 10, 2002 9:22 AM

Howdy -- I hope someone can help!

I want to search for a regular expression (3 capital letters followed by 3 numbers) in a text cell (it's a sentence). I'd like to print that 6 character "word". It looks like the search function can tell me if there is a 6 char word (but not letters/numbers), and only reports back the character location in the cell. Any thoughts?

Thanks!

Posted by Chirs D on January 10, 2002 12:22 PM

Not sure what you mean by wanting to print it, but if you use filtering, it will filter on all rows containing your ABC123 :

Data
Filter
Autofilter
-click the filter-
custom
-show rows where-
contains
"ABC123"

is this what you had in mind ?

HTH
Chris

Posted by Daniel Celotta on January 11, 2002 7:54 AM

Thanks for the thought, but that's not quite what I meant -- I'll try and clarify here.

I have a bunch of cells (~500) containing sentences. I would like to create a formula to put in the cell(s) to the right of each sentence to identify when specific key words are included in the sentence. This I can do, using FIND, but that only works if I know the precise word I'm looking for.

What I'd really like is to do is search for words that are in the format:

[A-Z][A-Z][A-Z][0-9][0-9][0-9]
(any three capital letters followed by any three numbers)

and then report back (fill in a cell) with that 6 character word (or at least the 3 letters of that word). Changing the view (or hiding cells) won't work for me, as I will be using this data in other formulas (counting the # of incidents of each type of program/word).

(my application for this, if you are interested, is that my team at work gets alphanumeric pages, to the tune of 500 / month. I want to categorize these pages to identify which programs (the 6 letter words) are waking them up at night the most. I am currently searching for words like "full" (for full files areas), "sql" (for when they need to perform an sql statement), "fail" for a failed job, etc.)

Thank You for any help!

--Dan