Extracting Text

B

Brian

I have a column of text that I would like Excel to search through and return
the specific text that I am searching for. Is there a way to do this?

Thanks for any help,
 
G

Gary''s Student

Let's say you want to see all the rows that contain the word "hello"

Select the column and pull-down:

Filter > Autofilter > Custom... > Contains > hello
 
M

Max

One guess ..

Assuming source text in A1:A10 ..

With partial text to be searched entered in B1,
put this in the formula bar for C1, and array-enter
(press CTRL+SHIFT+ENTER):

=INDEX(A1:A10,MATCH(TRUE,(ISNUMBER(SEARCH(B1,A1:A10))),0))

Change SEARCH to FIND if case sensitivity is required.
(SEARCH is not case sensitive)
 
G

GottaRun

Is this 'filter' from the 'Data' drop-down list?
After I did 'autofilter' it simply returns to the excel sheet.

I want to do somethiong similar.
I want all the rows where the cells in column B are not a blank. Then I
want to delete those and only those rows.

Suggestions ar welcomed.
 
M

Max

:
...
I want all the rows where the cells in col B are not a blank
I want to delete those and only those rows.
Click to expand...

Tinker on a *spare* copy ..

Select col B
Click Data > Filter > Autofilter
Choose "(NonBlanks)" from the autofilter droplist in top cell
Select the filtered "blue" row headers,
right-click on these > Delete Row
Remove autofilter

Note: Choose "(blanks)" from the droplist if you want to delete **blank**
rows
 
G

GottaRun

Sorry I don't follow. At one point autofilter says to select 1 cell only
and not a range of cells. My comments in () below.

Thank you so much for helping.
 
M

Max

GottaRun said:
Sorry I don't follow. At one point autofilter says to select 1 cell only
and not a range of cells. My comments in () below.
Click to expand...

If autofilter is already on, switch it off first (uncheck it)
(via Data > Filter > Autofilter)

As above, try switch autofilter off first, then select col B and switch it
on
You should get an autofilter droplist (arrow) in the top cell, i.e. in B1
(Presume you have a col label in B1?)

When you click on the droplist in B1,
if col B has blank cells in between data cells
(this was presumed from your orig. post)
then the droplist in B1 will show 2 options right at the bottom
(you may need to scroll down the droplist to see), viz.:

(Blanks)
(Non Blanks)
I want to delete rows where the cell in Column B is blank, not
the same as blank rows, i.e there could be non-blanks in other columns)
Click to expand...

Yes, I think I read it right earlier.
The steps given above will delete *entire* rows where col B is blank

Choose "(Blanks)" from the droplist, then do the ensuing steps:
 
G

GottaRun

Terrific .. got it!

Unfortunately what I took as a non-blank in cells of column B is som
icon - it displays a small magnifying glass inside the cell. What
want to do is to delete the rows where the cells of column B has thi
magnifying glass. Autofilter treted all the cells in B as blank.

This is data I 'copy&paste' from a website. file attached inside
zip.

thanks again for the detailed explanation .. will come in handy

+-------------------------------------------------------------------
|Filename: Book4.zip
|Download: http://www.excelforum.com/attachment.php?postid=4427
+-------------------------------------------------------------------
 
M

Max

.. icon - it displays a small magnifying glass inside the cell.

These are objects/shapes, floating on top of cells, not inside.

It is possible to select all objects on the sheet via:
Press F5 > Special > Objects > OK
and to delete these at one go

but .. I'm not sure if there is a way (maybe via vba ?)
to also select/delete the underlying cells/rows

Hang around awhile for views from others
You may also wish to put in a new post/query in .programming
 
Top