ExcelBanter - Extracting Text

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Extracting Text (https://www.excelbanter.com/excel-worksheet-functions/75388-extracting-text.html)

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,
--
Brian

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
--
Gary's Student

"Brian" wrote:

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,
--
Brian

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:A1 0))),0))

Change SEARCH to FIND if case sensitivity is required.
(SEARCH is not case sensitive)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brian" wrote in message
...
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,
--
Brian

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.

--
GottaRun
------------------------------------------------------------------------
GottaRun's Profile: http://www.excelforum.com/member.php...o&userid=31297
View this thread: http://www.excelforum.com/showthread...hreadid=519182

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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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.

Max Wrote:
"GottaRun" wrote:
...
I want all the rows where the cells in col B are not a blank
I want to delete those and only those rows.

Tinker on a *spare* copy ..

Select col B (i highlighted column B)
Click Data Filter Autofilter (check mark is on for autofilter,
right?)
Choose "(NonBlanks)" from the autofilter droplist in top cell (no idea
what this means- do I right-click inside that 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 ( 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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

--
GottaRun
------------------------------------------------------------------------
GottaRun's Profile: http://www.excelforum.com/member.php...o&userid=31297
View this thread: http://www.excelforum.com/showthread...hreadid=519182

"GottaRun" wrote:
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.

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

Tinker on a *spare* copy ..

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

Select col B (i highlighted column B)
Click Data Filter Autofilter (check mark is on for autofilter,
right?)
Choose "(NonBlanks)" from the autofilter droplist in top cell (no idea
what this means- do I right-click inside that cell?)

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)

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

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)

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:

Select the filtered "blue" row headers,
right-click on these Delete Row
Remove autofilter

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Terrific .. got it!

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

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

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

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

--
GottaRun
------------------------------------------------------------------------
GottaRun's Profile: http://www.excelforum.com/member.php...o&userid=31297
View this thread: http://www.excelforum.com/showthread...hreadid=519182

.. 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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"GottaRun" wrote in
message ...

Terrific .. got it!

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

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

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

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

--
GottaRun
------------------------------------------------------------------------
GottaRun's Profile:
http://www.excelforum.com/member.php...o&userid=31297
View this thread: http://www.excelforum.com/showthread...hreadid=519182